Delete trigger in SQL Server 7

[Aidan Whitehall]

Have gone through BOL and Google, but can't find the answer... please help
with a simple Q. I'm trying to create a simple cascade delete trigger in SQL
Server 7 where deleting "parent" records in table X delete corresponding
child records in table Y.

Table X
=========
X_ID
SOME_VAL

Table Y
=========
Y_ID
X_ID
SOME_VAL
When there is no relationship between X.X_ID and Y.X_ID, the following
trigger works fine:

CREATE TRIGGER "temp" ON x
FOR DELETE
AS

delete
from y
where x_id in (select x_id from deleted)

However, when a relationship is created to enforce referential integrity,
the trigger fails, with a "DELETE statement conflicted with COLUMN REFERENCE
constraint" error. I've seen examples where the trigger says (for example)
"AFTER INSERT", where presumably the code is specifically run after the
event that triggers it -- is there a way of forcing the trigger to run
before the delete from table X is executed? I've tried using "BEFORE
DELETE", but no dice :-\
Thanks!

--
Aidan Whitehall <ai************@fairbanks.co.uk>
Macromedia ColdFusion Developer
Fairbanks Environmental Ltd [phone]

[Dan Guzman]

Itzik Ben-Gan and Thomas Moreau have some examples of cascading RI triggers
in their white paper:
<http://www.msdn.microsoft.com/librar...y/en-us/dnsql2
k/html/sql_refintegrity.asp?frame=true>.

The main consideration is that you cannot have declarative referential
integrity in place when you perform cascading actions in triggers in SQL 7,
..

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Aidan Whitehall" <ai************@fairbanks.co.uk> wrote in message
news:bt**********@sparta.btinternet.com...

Have gone through BOL and Google, but can't find the answer... please help
with a simple Q. I'm trying to create a simple cascade delete trigger in SQL Server 7 where deleting "parent" records in table X delete corresponding
child records in table Y.

Table X
=========
X_ID
SOME_VAL

Table Y
=========
Y_ID
X_ID
SOME_VAL
When there is no relationship between X.X_ID and Y.X_ID, the following
trigger works fine:

CREATE TRIGGER "temp" ON x
FOR DELETE
AS

delete
from y
where x_id in (select x_id from deleted)

However, when a relationship is created to enforce referential integrity,
the trigger fails, with a "DELETE statement conflicted with COLUMN REFERENCE constraint" error. I've seen examples where the trigger says (for example)
"AFTER INSERT", where presumably the code is specifically run after the
event that triggers it -- is there a way of forcing the trigger to run
before the delete from table X is executed? I've tried using "BEFORE
DELETE", but no dice :-\
Thanks!

--
Aidan Whitehall <ai************@fairbanks.co.uk>
Macromedia ColdFusion Developer
Fairbanks Environmental Ltd [phone]

[Aidan Whitehall]

> Itzik Ben-Gan and Thomas Moreau have some examples of cascading RI
triggers

in their white paper:
<http://www.msdn.microsoft.com/librar...y/en-us/dnsql2 k/html/sql_refintegrity.asp?frame=true>.
K, thanks -- I'll check that out.

The main consideration is that you cannot have declarative referential
integrity in place when you perform cascading actions in triggers in SQL

7,

Damn, damn, damn.

Well, thanks for the clarification, anyway.
Regards

--
Aidan Whitehall <ai************@fairbanks.co.uk>
Macromedia ColdFusion Developer
Fairbanks Environmental Ltd [phone]

[Daniel Morgan]

Aidan Whitehall wrote:

The main consideration is that you cannot have declarative referential
integrity in place when you perform cascading actions in triggers in SQL

7,

Damn, damn, damn.

Well, thanks for the clarification, anyway.

If I may request it ... would someone please confirm the above statement
about SQL Server. This statement is untrue is other commercial RDBMS
products, such as Oracle and DB2, and I would be surprised if SQL Server
didn't allow this very basic functionality. Is is still true in the
current version?

Thanks.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

[Dan Guzman]

> If I may request it ... would someone please confirm the above statement

about SQL Server. This statement is untrue is other commercial RDBMS
products, such as Oracle and DB2, and I would be surprised if SQL Server
didn't allow this very basic functionality. Is is still true in the
current version?
One can implement cascading actions via DRI or INSTEAD OF triggers in
current version of SQL Server (SQL Server 2000).

Unfortunately, Aidan is using an older version (SQL 7) in which only the
AFTER trigger model is available. Consequently, it is necessary to enforce
referential integrity in triggers instead of DRI in cases when cascading
actions are performed in triggers. Another alternative is to perform
cascading functions via stored procedures.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Daniel Morgan" <da******@x.washington.edu> wrote in message
news:1073846407.832632@yasure... Aidan Whitehall wrote:

The main consideration is that you cannot have declarative referential
integrity in place when you perform cascading actions in triggers in SQL

7,

Damn, damn, damn.

Well, thanks for the clarification, anyway.

If I may request it ... would someone please confirm the above statement
about SQL Server. This statement is untrue is other commercial RDBMS
products, such as Oracle and DB2, and I would be surprised if SQL Server
didn't allow this very basic functionality. Is is still true in the
current version?

Thanks.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

[Daniel Morgan]

Dan Guzman wrote:

If I may request it ... would someone please confirm the above statement
about SQL Server. This statement is untrue is other commercial RDBMS
products, such as Oracle and DB2, and I would be surprised if SQL Server
didn't allow this very basic functionality. Is is still true in the
current version?

One can implement cascading actions via DRI or INSTEAD OF triggers in
current version of SQL Server (SQL Server 2000).

Unfortunately, Aidan is using an older version (SQL 7) in which only the
AFTER trigger model is available. Consequently, it is necessary to enforce
referential integrity in triggers instead of DRI in cases when cascading
actions are performed in triggers. Another alternative is to perform
cascading functions via stored procedures.

Thanks. I was a bit surprised that such basic functionality might not
exist. In Oracle one can always perform cascading deletes in any code
but the referential constraint, itself, will perform the function. For
example:

ALTER TABLE child
ADD CONSTRAINT fk_child_parent
FOREIGN KEY (test)
REFERENCES parent (test)
ON DELETE CASCADE;

Thanks again for the clarification.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

[Dan Guzman]

> Thanks. I was a bit surprised that such basic functionality might not

exist. In Oracle one can always perform cascading deletes in any code
but the referential constraint, itself, will perform the function. For
example:

ALTER TABLE child
ADD CONSTRAINT fk_child_parent
FOREIGN KEY (test)
REFERENCES parent (test)
ON DELETE CASCADE;

The same syntax works in SQL 2000. Thank goodness for ANSI standards ;-)

BEGIN TRAN

CREATE TABLE parent(test int NOT NULL PRIMARY KEY)
CREATE TABLE child(test int NOT NULL PRIMARY KEY)

ALTER TABLE child
ADD CONSTRAINT fk_child_parent
FOREIGN KEY (test)
REFERENCES parent (test)
ON DELETE CASCADE;

insert into parent values(1)
insert into child values(1)

delete from parent
select * from parent --no rows
select * from child --no rows

ROLLBACK

--
Hope this helps.

Dan Guzman
SQL Server MVP

[Daniel Morgan]

Dan Guzman wrote:

Thanks. I was a bit surprised that such basic functionality might not
exist. In Oracle one can always perform cascading deletes in any code
but the referential constraint, itself, will perform the function. For
example:

ALTER TABLE child
ADD CONSTRAINT fk_child_parent
FOREIGN KEY (test)
REFERENCES parent (test)
ON DELETE CASCADE;

The same syntax works in SQL 2000. Thank goodness for ANSI standards ;-)

BEGIN TRAN

CREATE TABLE parent(test int NOT NULL PRIMARY KEY)
CREATE TABLE child(test int NOT NULL PRIMARY KEY)

ALTER TABLE child
ADD CONSTRAINT fk_child_parent
FOREIGN KEY (test)
REFERENCES parent (test)
ON DELETE CASCADE;

insert into parent values(1)
insert into child values(1)

delete from parent
select * from parent --no rows
select * from child --no rows

ROLLBACK

Excellent. Thanks.

Like I said ... I would have been quite surprised if it didn't exist.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)