Questions about if statements in triggers

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

Questions about if statements in triggers

Author

Topic

intern2424
Yak Posting Veteran

53 Posts

Posted - 2009-12-06 : 16:00:58

I was wondering if you can do an if statement like is Insert run the update else if Delete run update? Right now I two triggers. I have been trying to create just one.

Alter TRIGGER insert_update_Employees
ON Assignment
FOR INSERT
AS
UPDATE P
SET Num_Emp = num.Num_Emp
FROM
Project P
INNER JOIN

( Select Proj_num, [Num_Emp] = Count(Emp_num)
From Assignment
Group by Proj_Num) num

ON

p.proj_num = num.proj_num

PRINT 'Emp_Num has been Updated.';

Thanks for any help you can give.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-06 : 16:19:40

If you are running the same UPDATE statement, you don't need an IF statement as far as I can tell. Just make it an INSERT/DELETE trigger.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

intern2424
Yak Posting Veteran

53 Posts

Posted - 2009-12-06 : 16:29:53

I was just wondering if I can create one trigger to work with INSERT OR DELETE STATEMENT instead of two. Like insert(John, doe, 27, 1982, 15) I will insert john doe and update Num_emp but also if i run a delete on john doe it will update emp_num from the same trigger. Is this even possible or do I just have to use two triggers.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-06 : 16:37:48

Yes, that is what I was saying in my last post. Please see CREATE TRIGGER in SQL Server Books Online for more details.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-06 : 17:32:41

create your trigger for insert,delete.
check trigger tables inserted and deleted.
if only deleted has values then there was no update.

No, you're never too old to Yak'n'Roll if you're too young to die.

intern2424
Yak Posting Veteran

53 Posts

Posted - 2009-12-06 : 19:00:24

sorry for the double post.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources