help on custom log - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

help on custom log

Author

Topic

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2005-04-05 : 10:15:43

Some time ago Bret placed a code to create triggers to trace when inserts, updates and deletions were made to any table. I tried that code and it worked great but now I'm inserting some rows from the QA and the insertion is not being registrated on the log tables so I thought the triggers were deleted or something but when I edited the table directly from the EM the change was registered in the log tables. Do you have any idea why this is happening

Here is the trigger code for the table I see a corresponding record in the log table:


CREATE TRIGGER dbo_myTable_TR ON dbo.myTable FOR UPDATE, DELETE AS  

DECLARE @HOSTNAME sysname, @DESC varchar(50)  

SELECT @HOSTNAME = hostname 
From master.dbo.sysprocesses 
Where spid = @@SPID  

IF EXISTS(SELECT * FROM myTable) 
     SELECT @DESC = '' ELSE SELECT @DESC = 'MASS DELETE' 

If Exists (Select * From Inserted) And Exists (Select * From Deleted)  
     INSERT INTO log_myTable 
     (HIST_ADD_TYPE, HIST_ADD_SYSTEM_USER, HIST_ADD_USER_NAME, 
      HIST_ADD_HOSTNAME, HIST_ADD_SPID, HIST_ADD_DESC, [myTable's fields]) 
     SELECT  'U', SYSTEM_USER, USER_NAME(), @HOSTNAME, @@SPID, @DESC, [myTable's fields]
     FROM deleted 

If Not Exists (Select * From Inserted) And Exists (Select * From Deleted)  And @DESC = ''  
     INSERT INTO log_myTable (   HIST_ADD_TYPE, HIST_ADD_SYSTEM_USER, 
     HIST_ADD_USER_NAME, HIST_ADD_HOSTNAME, HIST_ADD_SPID, HIST_ADD_DESC, 
     [myTable's fields]) 
     SELECT    'D', SYSTEM_USER, USER_NAME(), @HOSTNAME, @@SPID, @DESC, [myTable's fields]
     FROM deleted 

If Not Exists (Select * From Inserted) And Exists (Select * From Deleted)  And @DESC <> ''   
     INSERT INTO log_myTable(   HIST_ADD_TYPE, HIST_ADD_SYSTEM_USER,  
     HIST_ADD_USER_NAME, HIST_ADD_HOSTNAME, HIST_ADD_SPID, HIST_ADD_DESC,  
     [myTable's fields])  
     SELECT  TOP 1  'D', SYSTEM_USER, USER_NAME(), @HOSTNAME, @@SPID, @DESC,  
     [myTable's fields] 
     FROM deleted

.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.
A candle loses nothing by lighting another candle

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-12 : 17:38:42

quote:
Originally posted by tuenty

I tried that code and it worked great but now I'm inserting some rows from the QA and the insertion is not being registrated on the log tables so I thought the triggers were deleted or something but when I edited the table directly from the EM the change was registered in the log tables.

Your trigger code handles only updates and deletes and NOT inserts. When you edited the data in EM, you probably did an update which caused the trigger to fire. Inserts are not causing it to fire.

Tara

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2005-04-12 : 17:48:25

Thanx (I've been blind)

Well if I'm adding the records from deleted then all the record should have already existed

So I would have to add, if I wanted to record insertions, this code:


If Exists (Select * From Inserted) And not Exists (Select * From Deleted)  
     INSERT INTO log_myTable 
     (HIST_ADD_TYPE, HIST_ADD_SYSTEM_USER, HIST_ADD_USER_NAME, 
      HIST_ADD_HOSTNAME, HIST_ADD_SPID, HIST_ADD_DESC, [myTable's fields]) 
     SELECT  'U', SYSTEM_USER, USER_NAME(), @HOSTNAME, @@SPID, @DESC, [myTable's fields]
     FROM Inserted

.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.
A candle loses nothing by lighting another candle

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-12 : 17:53:55

The key is here: CREATE TRIGGER dbo_myTable_TR ON dbo.myTable FOR UPDATE, DELETE. Look up CREATE TRIGGER for more information.

Tara

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2005-04-13 : 09:14:18

ok I got it Thanks!!