beginner 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

beginner Trigger

Author

Topic

viralbhatt
Starting Member

3 Posts

Posted - 2009-03-16 : 06:10:46

Create Trigger tri_AuditInsUpd
on [dbo].[TextDesc]
For Insert, Update
As
DECLARE
@IDRecNo int
BEGIN
select @IDRecNo = ID
from Inserted
Insert into tbl_audits(recordid, username, auddatetime, typeofupdate)
select @IDRecNo, SYSTEM_USER, getdate(), 1,
END

Viral Bhatt

viralbhatt
Starting Member

3 Posts

Posted - 2009-03-16 : 06:21:09

my mistake. I forgot to add description.

do I need three different type of triggers for Insert update or delete. I have main table and audit table. any changes (insert, update, delete) made to main table will write audit to the audit table. how can I tell if it's insert, update or delete. I want to add 1 for insert, 2 for update and 3 for delete. at the moment I have three triggers doing this job.

Viral Bhatt

viralbhatt
Starting Member

3 Posts

Posted - 2009-03-16 : 06:23:05

there's no comma after 1 in select statement.

Viral Bhatt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-16 : 10:41:07

you're assuming that inserted will always contain a single record which is not true. as such, the trigger should be like


Create Trigger tri_AuditInsUpd
on [dbo].[TextDesc]
For Insert, Update,Delete 
As
BEGIN
	
Insert into tbl_audits(recordid, username, auddatetime, typeofupdate) 
select COALESCE(i.ID,d.ID), SYSTEM_USER, getdate(), 
CASE WHEN i.ID IS NOT NULL AND d.ID IS NULL THEN 1--insert
     WHEN i.ID IS NOT NULL AND d.ID IS NOT NULL THEN 2 --Update
     WHEN d.ID IS NOT NULL AND i.ID IS NULL THEN 3 --Delete
END 
from Inserted i
FULL OUTER JOIN Deleted d
ON i.ID=d.ID 
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources