TRIGGER的使用（修改SP自动触发） 

 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)

 

posted @ 2014-06-20 09:18  bkyshj  阅读(261)  评论(0)    收藏  举报