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 2005 Forums

Transact-SQL (2005)

Trigger

Author

Topic

Steve2106
Posting Yak Master

183 Posts

Posted - 2011-03-23 : 17:33:04

Hi There,
In need of your help again.

I have never created a trigger before and was wondering if you could help.

I have a table that has an isDeleted column. When I update the table and the isDeleted is set to true I would like to use a trigger to run a stored procedure to move the record to an archive table.

How wuld I go about that?

As always really appreciate your help.

Best regards,

Steve

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-03-24 : 04:37:24

Create an update trigger with this code

insert into archive_table(col_list)
select col_list from updated where isDeleted='true'

Madhivanan

Failing to plan is Planning to fail

Steve2106
Posting Yak Master

183 Posts

Posted - 2011-03-24 : 07:30:23

Hi Madhivanan,
Thanks for the reply.

Maybe my post was not written well.
Your code to insert a record into the archive table is good and I already know how to do this.
I need to know how I write the trigger code.
If a record is marked as IsDeleted, I would like to know the update trigger code that executes my stored procedure to insert the record into archive table.

Thanks

Steve

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-03-24 : 09:05:50

Create Trigger MyTrigger
On MyTable
After Update, Insert
As

Declare @keys Table (
KeyId type
)
Insert Into @keys
Select KeyId From Inserted Where IsDeleted = 1

Declare @keyId type

while exists(Select * From @keys)
Begin

Set @KeyId = (Select top 1 keyId From @Keys)

Exec ArchiveProcedure @KeyId

Delete From @Keys Where KeyId = @KeyId

End
Go

Would be cleaner if the move was in the trigger and not a procedure

Create Trigger MyTrigger
On MyTable
After Update, Insert
As
Begin

Insert Into ArchiveTable
Select Col01, Col02,... From Inserted Where IsDeleted = 1
/*
-- And Maybe a clean out also ??
Delete
From MyTable A
Inner Join Inserted B
On A.KeyId = B.KeyId
Where B.IsDeleted = 1
*/
End

Corey

snSQL on previous signature "...Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources