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)
