database wide trigger firing with update

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 2005 Forums

Transact-SQL (2005)

database wide trigger firing with update

Author

Topic

overlordqd
Starting Member

3 Posts

Posted - 2008-10-22 : 05:33:58

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.

[CODE]
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[/CODE]

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.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-10-22 : 06:27:21

check about "COLUMNS_UPDATED ( )" in BOL


"There is only one difference between a dream and an aim. A dream requires soundless sleep to see, whereas an aim requires sleepless efforts to achieve..!!"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-22 : 06:33:18

[code]set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[forumchange] ON test2
FOR UPDATE
AS
BEGIN
insert into test3 (oldNAME,newNAME,date)
SELECT d.field1,i.field1,d.field2,i.field2,...,GETDATE()
FROM DELETED d
INNER JOIN INSERTED i
ON i.PK=d.PK
END[/code]PK is your primary key

overlordqd
Starting Member

3 Posts

Posted - 2008-10-22 : 08:30:38

quote:

Originally posted by visakh16

set ANSI_NULLS ON 
set QUOTED_IDENTIFIER ON 
GO 
ALTER TRIGGER [dbo].[forumchange] ON test2 
FOR UPDATE 
AS 
BEGIN 
insert into test3 (oldNAME,newNAME,date) 
SELECT d.field1,i.field1,d.field2,i.field2,...,GETDATE()
FROM DELETED d
INNER JOIN INSERTED i
ON i.PK=d.PK
END

PK is your primary key

thanks for this ! now, we got all of the column.

one last thing. i need the table and the field name. how do we get the name of the field and table those are being updated .

thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-22 : 09:56:07

i dont thnk you can get that in DML trigger. you might have to hardcode values there?

overlordqd
Starting Member

3 Posts

Posted - 2008-10-23 : 09:49:16

quote:
Originally posted by visakh16

i dont thnk you can get that in DML trigger. you might have to hardcode values there?

i see, thanks. but what do you mean by "hardcoding" ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 09:53:32

quote:
Originally posted by overlordqd

quote:
Originally posted by visakh16

i dont thnk you can get that in DML trigger. you might have to hardcode values there?

i see, thanks. but what do you mean by "hardcoding" ?

simply type in value yourselves as below

set ANSI_NULLS ON 
set QUOTED_IDENTIFIER ON 
GO 
ALTER TRIGGER [dbo].[forumchange] ON test2 
FOR UPDATE 
AS 
BEGIN 
insert into test3 (tablename,oldNAME,newNAME,date) 
SELECT 'forumchange',d.field1,i.field1,d.field2,i.field2,...,GETDATE()
FROM DELETED d
INNER JOIN INSERTED i
ON i.PK=d.PK
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources