b2TcBd8f

1CREATE TABLE aaa_log (
2   [id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
3   [name] [varchar](50) NOT NULL,
4   [op] [varchar](50) NULL)
5	
6declare @cmd varchar(max)
7declare trigger_create cursor for
8select 'Create trigger ['+TABLE_SCHEMA+'].[xxtr_'+TABLE_NAME+'_auto]
9  on ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] fro insert,update,delete as
10  BEGIN
11   declare @op varchar(20)
12   if exists(SELECT * from inserted)  and exists(SELECT * from deleted)
13   begin
14     set @op = ''update''
15   end
16   if exists(SELECT * from inserted)  and not exists(SELECT * from deleted)
17   begin
18     set @op = ''insert''
19   end
20   if not exists(SELECT * from inserted)  and exists(SELECT * from deleted)
21   begin
22     set @op = ''delete''
23   end
24   insert into aaa_log([name],[op]) values('+TABLE_SCHEMA+'.'+TABLE_NAME+', @op)
25  END
26'
27from information_schema.tables
28where table_type='BASE TABLE' AND table_name <> 'aaa_log'
29
30open trigger_create
31fetch next from trigger_create into @sql
32while @@FETCH_STATUS =0
33BEGIN 
34  exec(@sql)
35  fetch next from trigger_create into @sql
36END
37close trigger_create
38deallocate trigger_create
39	
40CREATE TABLE dbo.floob(a int);
41INSERT dbo.floob(a) VALUES(1);
42GO
43
44CREATE TRIGGER dbo.TRfloob
45ON dbo.floob
46FOR INSERT, UPDATE, DELETE
47AS
48BEGIN
49  IF    NOT EXISTS (SELECT 1 FROM inserted) 
50    AND NOT EXISTS (SELECT 1 FROM deleted)
51  BEGIN
52    PRINT 'Strange, unknown operation!';
53  END
54END
55GO
56
57UPDATE dbo.floob SET a = 2 WHERE a = 2;
58GO
59	
60IF     NOT EXISTS (SELECT 1 FROM inserted) 
61   AND NOT EXISTS (SELECT 1 FROM deleted)
62BEGIN
63  RETURN;
64END