1
CREATE
TRIGGER
[
trg_save_change_SP
]
2
ON
DATABASE
3
FOR
CREATE_PROCEDURE, ALTER_PROCEDURE,DROP_PROCEDURE
4
AS
5
DECLARE
@data
XML
6
DECLARE
@InstanceName
nvarchar
(
200
),
7
@DBName
nvarchar
(
100
) ,
8
@ObjectID
int
,
9
@Version
int
,
10
@DBUser
nvarchar
(
100
),
11
@InDateTime
datetime
,
12
@HostName
nvarchar
(
200
),
13
@LoginName
nvarchar
(
100
),
14
@EventName
nvarchar
(
100
),
15
@ObjectName
nvarchar
(
200
) ,
16
@TSQL
nvarchar
(
max
),
17
@ObjectType
char
(
2
),
18
@SeqNo
int
19
20
SET
@data
=
EVENTDATA()
21
22
SELECT
23
@InstanceName
=
@@SERVERNAME
,
24
@DBName
=
DB_NAME
(),
25
@HostName
=
hostname,
26
@DBUser
=
CONVERT
(
nvarchar
(
100
),
CURRENT_USER
),
27
@LoginName
=
@data
.value(
'
(/EVENT_INSTANCE/LoginName)[1]
'
,
'
nvarchar(100)
'
),
28
@EventName
=
@data
.value(
'
(/EVENT_INSTANCE/EventType)[1]
'
,
'
nvarchar(100)
'
),
29
@ObjectName
=
@data
.value(
'
(/EVENT_INSTANCE/ObjectName)[1]
'
,
'
nvarchar(400)
'
),
30
@TSQL
=
@data
.value(
'
(/EVENT_INSTANCE/TSQLCommand)[1]
'
,
'
nvarchar(max)
'
)
31
FROM
master..sysprocesses
WHERE
spid
=
@@spid
32
33
SELECT
@Version
=
ISNULL
(
MAX
(Version),
0
)
+
1
FROM
admin.dbo.ObjectLog
WHERE
ObjectName
=
@ObjectName
AND
DBName
=
@DBName
34
35
SELECT
@ObjectType
=
type
FROM
sys.objects
WHERE
name
=
@ObjectName
36
37
SELECT
@SeqNo
=
ISNULL
(
MAX
(SeqNo),
0
)
+
1
FROM
admin.dbo.ObjectLog
38
39
INSERT
admin.dbo.ObjectLog(
40
[
SeqNo
]
41
,
[
DBName
]
42
,
[
ObjectID
]
43
,
[
ObjectName
]
44
,
[
Version
]
45
,
[
EventName
]
46
,
[
DBUser
]
47
,
[
HostName
]
48
,
[
LoginName
]
49
,
[
InDateTime
]
50
,
[
TSQL
]
51
,
[
CheckInChk
]
52
,
[
InstanceName
]
53
,
[
ObjectType
]
54
)
55
VALUES
(
56
@SeqNo
57
,
@DBName
58
,
Object_ID
(
@ObjectName
)
59
,
@ObjectName
60
,
@Version
61
,
@EventName
62
,
@DBUser
63
,
LTRIM
(
RTRIM
(
@HostName
))
64
,
@LoginName
65
,
GETDATE
()
66
,
@TSQL
67
,
'
0
'
68
,
@InstanceName
69
,
@ObjectType
)
