|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Friday, October 03, 2008 3:43 AM
Points: 3,
Visits: 7
|
|
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
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Yesterday @ 9:01 PM
Points: 20,164,
Visits: 13,702
|
|
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".
"Data isn't the only thing that's supposed to have Integrity."
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Friday, October 03, 2008 3:43 AM
Points: 3,
Visits: 7
|
|
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
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 11:06 AM
Points: 17,125,
Visits: 12,226
|
|
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
We walk in the dark places no others will enter
We stand on the bridge and none may pass
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Friday, October 03, 2008 3:43 AM
Points: 3,
Visits: 7
|
|
Brilliant, Thanks alot for your help guys :D
I spent ages trying to figure this out!!
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: 2 days ago @ 12:32 PM
Points: 1,754,
Visits: 3,036
|
|
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.
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Yesterday @ 9:01 PM
Points: 20,164,
Visits: 13,702
|
|
GilaMonster (4/14/2008)
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
Agreed... coffee deprivation... I've fixed my post.
--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".
"Data isn't the only thing that's supposed to have Integrity."
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Monday, September 29, 2008 6:35 AM
Points: 5,
Visits: 5
|
|
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
|
|
|
|
|
SSCrazy Eights
Group: General Forum Members
Last Login: 2 days ago @ 2:45 PM
Points: 8,681,
Visits: 4,953
|
|
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?
- GSquared
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Monday, September 29, 2008 6:35 AM
Points: 5,
Visits: 5
|
|
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!!!
|
|
|
|
