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

trigger

Author

Topic

asm
Posting Yak Master

140 Posts

Posted - 2010-05-30 : 12:43:30

I create a two table i.e. Table1 and Table2,
And want to write a trigger on delete when any record from Table1 is deleted..
that record is inserted into Table2.

Please guide how to write trigger... because i never use trigger...

thanks

Sachin.Nand

2937 Posts

Posted - 2010-05-30 : 13:03:01

Why you want to use a trigger?
You can set a relationship between the 2 tables and set the Cascade Delete On.

PBUH

asm
Posting Yak Master

140 Posts

Posted - 2010-05-30 : 13:07:49

Can you explain how i get the result from relationship...
Delete from one table

that is inserted into another table

thanks

Sachin.Nand

2937 Posts

Posted - 2010-05-30 : 13:14:57

Oh sorry I totally misunderstood your requirement.I thought you want to DELETE it from table2.
By the way how are you deleting from Table1?

PBUH

Kristen
Test

22859 Posts

Posted - 2010-05-30 : 13:52:43

[code]
CREATE TRIGGER MyTriggerName ON TableA
AFTER DELETE
AS
SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON

INSERT INTO TableB
(
B_Col1, B_Col2, ...
)
SELECT A_Col1, A_Col2, ...
FROM deleted
[/code]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-30 : 13:55:55

Here is a bit more to see and test:

-- create the testtables
create table table_a (id int, test varchar(255))
create table table_b (id int, test varchar(255))

-- inserting testdata into table_a
insert table_a
select 1, 'test1' union all
select 2, 'test2'

-- show records of table_a
select * from table_a

-- see that there are no records in table_b
select * from table_b

go
-- create the delete-trigger on table_a
CREATE TRIGGER tr_table_a_DELETE
ON table_a
FOR DELETE
AS
set nocount on
insert table_b 
select * from deleted

go
-- now delete a record from table_a
delete from table_a where id=1

-- show records of table_a
select * from table_a

-- see the deleted record is now in table_b
select * from table_b

-- drop all that test-stuff
drop table table_a
drop table table_b

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

Sachin.Nand

2937 Posts

Posted - 2010-05-30 : 13:58:19

I wanted to suggest the OP to use output deleted clause.

PBUH

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-30 : 14:01:36

quote:
Originally posted by Idera

I wanted to suggest the OP to use output deleted clause.

PBUH

Yes you can do that - why not?

But the the OP has to consider that on ALL delete statements while a trigger is at one point only.

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

Sachin.Nand

2937 Posts

Posted - 2010-05-30 : 14:11:06

quote:
Originally posted by webfred

quote:
Originally posted by Idera

I wanted to suggest the OP to use output deleted clause.

PBUH

Yes you can do that - why not?

But the the OP has to consider that on ALL delete statements while a trigger is at one point only.

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

Yes thats the exact reason why I asked him how is his data getting deleted.If its only a one way deletion from a SP then wanted to suggest him the output clause else wanted to suggest him on the trigger.

PBUH

Kristen
Test

22859 Posts

Posted - 2010-05-31 : 02:39:26

If this is for "Audit" purposes then it has to be a trigger. Cannot rely on absolutely 100% of deletes going through an SProc. Sysadmins may delete stuff ... and forget to copy them to Audit table

kashyapsid
Yak Posting Veteran

78 Posts

Posted - 2010-06-01 : 03:35:03

can we use a trigger in all conditions like joins or delete or etc....

KaShYaP

kashyapsid
Yak Posting Veteran

78 Posts

Posted - 2010-06-01 : 03:40:28

this code really nice and it supports me to understand very well

quote:

Originally posted by webfred

Here is a bit more to see and test:

-- create the testtables
create table table_a (id int, test varchar(255))
create table table_b (id int, test varchar(255))

-- inserting testdata into table_a
insert table_a
select 1, 'test1' union all
select 2, 'test2'

-- show records of table_a
select * from table_a

-- see that there are no records in table_b
select * from table_b

go
-- create the delete-trigger on table_a
CREATE TRIGGER tr_table_a_DELETE
ON table_a
FOR DELETE
AS
set nocount on
insert table_b 
select * from deleted

go
-- now delete a record from table_a
delete from table_a where id=1

-- show records of table_a
select * from table_a

-- see the deleted record is now in table_b
select * from table_b

-- drop all that test-stuff
drop table table_a
drop table table_b

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

KaShYaP

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-01 : 03:41:59

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources