Trigger Question - 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

General SQL Server Forums

New to SQL Server Programming

Trigger Question

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2011-01-26 : 09:11:44

I'm trying to audit a particular field in a table. Any time the value of it is changed to 0 I would like to write a record to another table.

I'm not looking for new records being inserted but just existing records where the field will be changed to zero.

insert into LastAgeTable(
cus_no,
date
select
arcusfil_sql.cus_no,
getdate()
from arcusfil_sql join LastAgeTable on arcusfil_sql.cus_no = LastAgeTable.cus_no

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-01-26 : 10:20:28

Whats the question?

If you are looking to add a trigger, add an update trigger.

Vack
Aged Yak Warrior

530 Posts

Posted - 2011-01-26 : 10:41:15

I'm not sure if what I have will work.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-01-26 : 10:54:46

What do you have? You've posted an insert statement. If you have an update statement then it may work, it may not, but I can't see your database or screen, so please post it..

Vack
Aged Yak Warrior

530 Posts

Posted - 2011-01-26 : 12:10:34

create trigger(audit)
on update
insert into LastAgeTable(
cus_no,
date
select
arcusfil_sql.cus_no,
getdate()
from arcusfil_sql join LastAgeTable on arcusfil_sql.cus_no = LastAgeTable.cus_no

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-26 : 12:22:32

this will keep on inserting all records returned by join in lastagetable always during update. you need to use inserted,deleted tables in join to identify the updated records alone

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Vack
Aged Yak Warrior

530 Posts

Posted - 2011-01-26 : 13:32:25

So if I update an existing record that goes to the deleted table?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-01-27 : 04:06:33

No, it goes to both the inserted and deleted tables, that is the easiest way to ensure that it is an update as it will be in both.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-27 : 12:02:50

quote:
Originally posted by Vack

So if I update an existing record that goes to the deleted table?

If you update an existing record, old values go to deleted and new values go to inserted table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources