database wide trigger, firing with update event

[overlordqd]

i all,

i want to do something like that; if someone tries to update a table, the trigger will fire and add a new record that contains the field name, old value and new value.

i have already done a small part of it.

the code is below.

set ANSI_NULLS ON 
set QUOTED_IDENTIFIER ON 
GO 
ALTER TRIGGER [dbo].[forumchange] ON test2 
FOR UPDATE 
AS 
BEGIN 
Declare @newNAME varchar(64) 
Declare @oldNAME varchar(64) 
declare @date datetime 
Select @oldNAME = ad from deleted 
Select @newNAME = ad from inserted 
select @date = getdate() 
if (@newNAME is not null) and (@oldNAME is not null) 
insert into test3 (oldNAME,newNAME,date) 
values (@oldname,@newname,@date) 
END

as you see, the trigger fires up with any update on test2 db.

but current code only controls the "name" field of the table name_surname.
i want it to go after every field and log them.

thank you.

 

[SQL_Hell]

Hi,

Triggers are at table level not database level, see below

CREATE TRIGGER [ schema_name . ]trigger_name 
[COLOR="Red"]ON { table | view } [/COLOR]
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF } 
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 
[ WITH APPEND ] 
[ NOT FOR REPLICATION ] 
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }

Can I ask what you are trying achieve?

and how is your data being inserted / updated? what appplication and how?

 

[overlordqd]

i got a table for logging. for example the name of the table is log1.

i want to log the changes that people make on different tables into the log table.

i want to log these(if its possible).

table name*, field name*, user name, computer name, date, old data, new data.

* which user has changed


i know how i can do the last 5.

i use access adp as front and sql server 05 as backend.

i simply want to log what and when the user changed data.

thanks, and sorry for bad english.

 

[SQL_Hell]

Hi again

When using an update trigger to get the old value and new value from the table, you need to query the inserted and deleted system tables. Much like you have already done.

See this example:

CREATE TRIGGER trig_updateAuthor 

ON authors 

FOR UPDATE 

AS

DECLARE @oldName VARCHAR(100) 

DECLARE @newName VARCHAR(100)

IF NOT UPDATE(au_fName) AND NOT UPDATE(au_lName) 

BEGIN 

RETURN 

END

SELECT @oldName = (SELECT au_fName + ' ' + au_lName FROM Deleted) 

SELECT @newName = (SELECT au_fName + ' ' + au_lName FROM Inserted)

PRINT 'Name changed from "' + @oldName + '" to "' + @newName + '"'

You will need to have a trigger for each table you want to record updates for
Have a go at it and let me know how you get on