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
)
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
