I've added a simple table to a db called: aaa_log with columns :(
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?