How to handle in 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

SQL Server 2008 Forums

Transact-SQL (2008)

How to handle in Trigger

Author

Topic

keyursoni85
Posting Yak Master

233 Posts

Posted - 2011-06-08 : 10:03:03

Hi,
I have two tables Organisation and OrganisationGroup
I have created trigger which will fire on insert and update on organisation table.

I need operation like..
>> When i insert organisation trigger will add organisationGroup with same name as organisation if not found (which is done using insert trigger)
>> Problem >> Same thing should happen while update of Organisation
When I update organisation I want to update Its group with updated organisation name (I want to find organisation group with old name of organisation to update group)
? But in update trigger I do not have old Organisation Name to find Organisation group

How can I get old value of updated column in update trigger?
Please help

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-06-08 : 10:09:50

An update looks like: delete & insert
So:
deleted - has old record
inserted - has new record

Corey

I Has Returned!!

keyursoni85
Posting Yak Master

233 Posts

Posted - 2011-06-08 : 10:22:49

quote:
Originally posted by Seventhnight

An update looks like: delete & insert
So:
deleted - has old record
inserted - has new record

Corey

I Has Returned!!

I cannot delete current record .. its id references to other multiple tables..

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-06-08 : 10:30:41

You're not deleting it... you're updating it.

I'm explaining how it looks in the trigger.


Select
	NewValue = A.someColumn,
	OldValue = B.someColumn
From inserted A
Left Join deleted B
On A.yourKey = B.yourKey

Corey

I Has Returned!!

keyursoni85
Posting Yak Master

233 Posts

Posted - 2011-06-08 : 10:50:16

thank you very much..
but that trigger fires two times after update.. It fires two times.


ALTER TRIGGER utg_AddUpdateOrganisationGroup ON tbl_Organisation
FOR UPDATE
AS
BEGIN
	SET NOCOUNT ON;

any idea?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-06-08 : 11:09:01

If you have recursion on, and update the table the trigger is on... you can get multiple fires. Consider changing your update so that it only processes the first occurence.

Corey

I Has Returned!!

keyursoni85
Posting Yak Master

233 Posts

Posted - 2011-06-08 : 11:14:29

ok fine..but it fires only one for update organisation still trigger fires two times..

I will look into it..
thank you for your quick help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources