Doubt on trigger - 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

Doubt on trigger

Author

Topic

mageshks
Yak Posting Veteran

59 Posts

Posted - 2007-06-08 : 08:56:29

This is my table structure

EMPId EMpName PhoneNO
------------------------

and in my application i may change the " PhoneNo" Column for a empid.

I want to audit the PhoneNo change.

I have one more audit table say, EmpAudit where i have phoneno as a column

If there is any phoneNumber change for a particulare EMPID, i want to put a new phone no to the audit table.

How to implement this with out using INSTEADOF trigger???

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-06-08 : 10:29:07

With an UPDATE trigger.

e4 d5 xd5 Nf6

NeilG
Aged Yak Warrior

530 Posts

Posted - 2007-06-08 : 10:44:22

Use and Update trigger to the enter the new phone number from the update into the empaudit table

create trigger upd_Emp_phoneno
on employees
after update
as
update empaudit
set empaudit.phoneno = inserted.phonono
where empaudit.empid = inserted.empid

mageshks
Yak Posting Veteran

59 Posts

Posted - 2007-06-08 : 11:29:49

Here I can update the phone number with the same number.In that case there shoulnt be a entry in to the audit table.
That is , there has to be a new entry only if the new phone numer is differ from the old number.
Can you give a solution for this case .
Thanks in advance

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-08 : 11:34:52

create trigger x on tbl for update, delete
as
insert audittbl
select d.colvalue, i.colvalue
from deleted d
left join inserted i
on d.id = i.id
where i.colvalue <> d.colvalue or (i.colvalue is null and d.colvalue is not null) or (d.colvalue is null and i.colvalue is not null)
go

have a look at

http://www.nigelrivett.net/SQLTriggers/Triggers_2_Creating_Audit_Trails.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mageshks
Yak Posting Veteran

59 Posts

Posted - 2007-06-08 : 11:37:26

Thanks >>>>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources