I've added a simple table to a db called: aaa_log with columns :(id
,name
, op
))
CREATE TABLE aaa_log (
[id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[name] [varchar](50) NOT NULL,
[op] [varchar](50) NULL)
id
column used just in manner to keep the order.
I've added a trigger (for insert, update, delete
) to all other tables in a db ussing the following script:
declare @cmd varchar(max)
declare trigger_create cursor for
select 'Create trigger ['+TABLE_SCHEMA+'].[xxtr_'+TABLE_NAME+'_auto]
on ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] fro insert,update,delete as
BEGIN
declare @op varchar(20)
if exists(SELECT * from inserted) and exists(SELECT * from deleted)
begin
set @op = ''update''
end
if exists(SELECT * from inserted) and not exists(SELECT * from deleted)
begin
set @op = ''insert''
end
if not exists(SELECT * from inserted) and exists(SELECT * from deleted)
begin
set @op = ''delete''
end
insert into aaa_log([name],[op]) values('+TABLE_SCHEMA+'.'+TABLE_NAME+', @op)
END
'
from information_schema.tables
where table_type='BASE TABLE' AND table_name <> 'aaa_log'
open trigger_create
fetch next from trigger_create into @sql
while @@FETCH_STATUS =0
BEGIN
exec(@sql)
fetch next from trigger_create into @sql
END
close trigger_create
deallocate trigger_create
Nobody else update the aaa_log
table, just these triggers, but when I check the aaa_log
table I see some rows where op
is NULL.
The only option I can think off is that both inserted and updated is null, so how the trigger had been activated?
Any explenation?
DELETE TOP(0) FROM aaa_log
?