April 14, 2008 at 5:41 am
Hi,
I'm trying to create a trigger that will delete a row from a second table once i delete a row from the first table, I have this code,
CREATE TRIGGER On_Delete
ON tblActivityArchive
AFTER DELETE
AS
BEGIN
DECLARE @v_id9 nvarchar(50)
SET @v_id9 = (SELECT ActivityRef FROM deleted)
DELETE FROM tblJobLinesArchive
WHERE ActivityRef = @v_id9
END
This trigger works great at deleting the row from the second table but when multiple rows are deleted
it does not work?
Any ideas how to handle more than 1 row deletes?
Thanks 🙂
Andy
April 14, 2008 at 5:51 am
Yes... SQL Server Triggers are different than a lot of other RDBMS. In Oracle, for instance, you would do just as you have done except your would add "FOR EACH ROW" and the RBAR you wrote would work just fine.
In SQL Server, the INSERTED and DELETED objects are tables that contain EVERYTHING that was INSERTED or DELETED and you must write the code in a fashion to handle ALL rows instead of one at a time.
CREATE TRIGGER On_Delete
ON tblActivityArchive
AFTER DELETE
AS
BEGIN
DELETE dbo.tblJobLinesArchive
FROM dbo.tblJobLinesArchive a
INNER JOIN Deleted d
ON a.ActivityRef = d.ActivityRef
END
--Jeff Moden
Change is inevitable... Change for the better is not.
April 14, 2008 at 6:38 am
Hi,
Thanks for the quick reply, that looks great!!
Except, when i try run parse it as a query I get
this error.
Msg 156, Level 15, State 1, Procedure On_Delete, Line 9
Incorrect syntax near the keyword 'WHERE'.
But the code looks perfect so i'm at a little bit of a loss
as to why this is a problem (Can you tell i'm still new to
this 🙂
Thanks!!
Andy
April 14, 2008 at 6:43 am
The WHERE should be an ON
CREATE TRIGGER On_Delete
ON tblActivityArchive
AFTER DELETE
AS
BEGIN
DELETE dbo.tblJobLinesArchive
FROM dbo.tblJobLinesArchive a
INNER JOIN Deleted d ON a.ActivityRef = d.ActivityRef
END
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 14, 2008 at 6:54 am
Brilliant, Thanks alot for your help guys 😀
I spent ages trying to figure this out!!
April 14, 2008 at 6:57 am
It looks like you're happy, so just to mention in passing that there are circumstances where 'ON DELETE CASCADE' would be an alternative. Here's an example...
--Structure
CREATE TABLE t1 (Id INT PRIMARY KEY)
CREATE TABLE t2 (Id INT PRIMARY KEY, t1Id INT)
ALTER TABLE t2 ADD CONSTRAINT FK_t2_t1Id__t1_Id FOREIGN KEY (t1Id) REFERENCES t1(Id) ON DELETE CASCADE
--Data
INSERT t1 SELECT 1 UNION SELECT 2
INSERT t2 SELECT 1, 1 UNION SELECT 2, 1 UNION SELECT 3, 2
SELECT * FROM t1
SELECT * FROM t2
/*
Id
-----------
1
2
Id t1Id
----------- -----------
1 1
2 1
3 2
*/
--Delete
DELETE FROM t1 WHERE Id = 1
--Results
SELECT * FROM t1
SELECT * FROM t2
/*
Id
-----------
2
Id t1Id
----------- -----------
3 2
*/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
April 14, 2008 at 9:49 am
GilaMonster (4/14/2008)
The WHERE should be an ON
CREATE TRIGGER On_DeleteON tblActivityArchive
AFTER DELETE
AS
BEGIN
DELETE dbo.tblJobLinesArchive
FROM dbo.tblJobLinesArchive a
INNER JOIN Deleted d ON a.ActivityRef = d.ActivityRef
END
Agreed... coffee deprivation... I've fixed my post.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 26, 2008 at 12:07 pm
How do you get around Delete Cascade when there are more that 2 tables involved without a trigger? is there a way in 2008?
Thanks
September 26, 2008 at 1:10 pm
Lloyd Harrison (9/26/2008)
How do you get around Delete Cascade when there are more that 2 tables involved without a trigger? is there a way in 2008?Thanks
Like this:
create table DTest1 (
ID int identity primary key);
go
create table DTest2 (
ID int identity primary key,
T1ID int references dbo.dtest1(id) on delete cascade); -- First Table
go
create table DTest3 (
ID int identity primary key,
T2ID int references dbo.dtest2(id) on delete cascade); -- Chain from 2nd table
go
create table DTest4 (
ID int identity primary key,
T1ID int references dbo.dtest1(id) on delete cascade); -- First table
go
-- Populate for test
insert into dtest1
default values
go 10
insert into dtest2
select id
from dtest1;
insert into dtest3
select id
from dtest2;
insert into dtest4
select id
from dtest1;
-- Actual test
select *
from dtest1
where id = 1;
select *
from dtest2
where t1id = 1;
select *
from dtest3
where t2id = 1;
select *
from dtest4
where t1id = 1;
delete from dtest1 -- Chain delete
where id = 1;
select *
from dtest1
where id = 1;
select *
from dtest2
where t1id = 1;
select *
from dtest3
where t2id = 1;
select *
from dtest4
where t1id = 1;
Is that what you mean?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 26, 2008 at 1:47 pm
Yes, and thats really wired because I trued that, well sort of.
I tried the following:
alter table my_table2
add constraint fk_my_table2
foreign key (fk_id2)
references my_table1(pk_id)
on delete cascade
This worked, then tried this
alter table my_table3
add constraint fk_my_table3
foreign key (fk_id3)
references my_table1(pk_id)
on delete cascade
and got the following error:
Msg 1785, Level 16, State 0, Line 1
Introducing FOREIGN KEY constraint 'fk_my_table3'
on table 'my_table3' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
But when I tried your sample code, it worked...bizarre!!!
September 26, 2008 at 2:20 pm
That's because the chain of deletes points to the same table more than once IE :
A > B > C > A
If any letter comes in the delete sequence more than once, the server will throw that error. That is also why you can't use on delete cascade when using a self-reference FK (when the FK points on the same table).
I have a table like this :
ClientID
ClientID_BillTo
If I need to delete a client, I have to manually delete the ClientID_BillTo in the same delete command... same thing goes for updates.
September 26, 2008 at 2:36 pm
Many-to-many reference tables have a similar problem. Can't chain delete from both parents into those. Have to use triggers for that kind of thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 26, 2008 at 2:58 pm
Wait, I didnt think my chains overlapped.
Here:
ZIP>SCHOOL
ZIP>DISTRICT
My district tabel and school table have a reference to zip.
If I delete ZIP I want to delete both the school and district
So each table, school and district, would have a cascade delete in it that references the zip table.
No?
September 26, 2008 at 3:13 pm
Lloyd Harrison (9/26/2008)
Wait, I didnt think my chains overlapped.Here:
ZIP>SCHOOL
ZIP>DISTRICT
My district tabel and school table have a reference to zip.
If I delete ZIP I want to delete both the school and district
So each table, school and district, would have a cascade delete in it that references the zip table.
No?
That should work. Are you sure you don't have any other fk constraints laying around?
Can we see the full ddl scripts for all the tables in question.
P.S. 1 way to figure out if any other tables are involved is to create a new database diagram, add those 3 tables with the option to include all related tables to 2-3 levels. You'll see any other possible objects there.
Another gotcha is that you can have "test" fks that you forgot you have (happens once in a while in dev environements).
September 26, 2008 at 7:12 pm
ZIP is obviously a reference table. Someday, you may want to delete a ZIP with no impact on other tables. Cascading deletes is a pretty bad idea to begin with because deletes, by themselves, can get you into pretty big trouble especially if you have SOX or SEC to contend with. I realize a school program has no such impact, but you can still get into a heap of trouble with deletes... those problems can be magnified quite a bit by the use of cascading deletes. My personal feeling is that one business rule that shouldn't be enforced by FK's is that of cascading deletes.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply