INSERT deleted rows into a new table

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)

INSERT deleted rows into a new table

Author

Topic

ischenk
Starting Member

15 Posts

Posted - 2008-07-22 : 20:23:39

Hello again. I have another issue. I want to create an insert trigger to write the rows deleted from one table and put them into a seperate table with the same values. However, I'm not sure on how the syntax on this one might work. Can you give me some type of starting point? I'm basically doing this to learn about triggers, so if triggers do not seem to be the proper solution, it is just because I just want to know how to effectively use them. Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-22 : 20:27:36

This would be a deleted trigger. You can run inserts in a deleted trigger.

Use the deleted trigger table rather than inserted.

You should be learning about other areas of SQL Server rather than triggers as triggers should typically be avoided. There is so much else to learn.

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

Subscribe to my blog

ischenk
Starting Member

15 Posts

Posted - 2008-07-22 : 21:07:26

Oh I know. I'm just on the subject of triggers right now. I'm looking at UDF's as well. I will be moving off the subject soon enough. However, what might the code for the deleted trigger table look like? Do you have an example?

ischenk
Starting Member

15 Posts

Posted - 2008-07-22 : 21:38:20

Would this code work? I don't know how to test it without actually deleting rows.

use Northwind
go
create trigger dbo.tdArchiveOrders
on dbo.Orders
for delete
as
if exists
(delete from dbo.Orders)
begin
declare @deleted table
select @deleted = (select * from deleted)
insert into dbo.OrderArchive
values (@deleted)
end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-23 : 04:52:01

quote:
Originally posted by ischenk

Would this code work? I don't know how to test it without actually deleting rows.

use Northwind
go
create trigger dbo.tdArchiveOrders
on dbo.Orders
for delete
as
if exists
(delete from dbo.Orders)
begin
declare @deleted table
select @deleted = (select * from deleted)
insert into dbo.OrderArchive
values (@deleted)
end

it wont. you cant assign result sets to a variable. the trigger should be like this

use Northwind
go
create trigger dbo.tdArchiveOrders
on dbo.Orders
for delete
as
if exists
(select 1 from deleted)
begin

insert into dbo.OrderArchive
select * from deleted
end

make sure you replace * with actual columns from your orders table which you want to insert into OrderArchive

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 05:03:57

Why the use of EXISTS at all?

create trigger dbo.tdArchiveOrders on dbo.Orders
for delete

insert into dbo.OrderArchive
select * from deleted

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-23 : 05:12:04

quote:
Originally posted by Peso

Why the use of EXISTS at all?
create trigger dbo.tdArchiveOrders on dbo.Orders
for delete

insert into dbo.OrderArchive
select * from deleted

E 12°55'05.25"
N 56°04'39.16"

yeah..thts true..no need of if,thanks for the spot

raky
Aged Yak Warrior

767 Posts

Posted - 2008-07-23 : 07:31:25

quote:
Originally posted by Peso

Why the use of EXISTS at all?
create trigger dbo.tdArchiveOrders on dbo.Orders
for delete

insert into dbo.OrderArchive
select * from deleted

E 12°55'05.25"
N 56°04'39.16"

when i try similarly iam getting error. My trigger code and error message is given below. Please tell me y it is giving error message.

create trigger dbo.deltrig on dbo.test for delete
insert into dbo.OrderArchive
select personalid,companyid,firstname,lastname from deleted

Error Message:

Msg 156, Level 15, State 1, Procedure deltrig, Line 2
Incorrect syntax near the keyword 'insert'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 07:38:07

create trigger dbo.deltrig on dbo.test for delete
as
insert into dbo.OrderArchive
select personalid,companyid,firstname,lastname from deleted

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources