Delete trigger in SQL Server 7

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 <aidanwhitehall@fairbanks.co.uk>
Macromedia ColdFusion Developer
Fairbanks Environmental Ltd +44 (0)1695 51775

re: Delete trigger in SQL Server 7

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" <aidanwhitehall@fairbanks.co.uk> wrote in message
news:btph6b$kri$1@sparta.btinternet.com...[color=blue]
> 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[/color]
SQL[color=blue]
> 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[/color]
REFERENCE[color=blue]
> 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 <aidanwhitehall@fairbanks.co.uk>
> Macromedia ColdFusion Developer
> Fairbanks Environmental Ltd +44 (0)1695 51775
>
>[/color]

re: Delete trigger in SQL Server 7

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

K, thanks -- I'll check that out.

[color=blue]
> The main consideration is that you cannot have declarative referential
> integrity in place when you perform cascading actions in triggers in SQL[/color]
7,

Damn, damn, damn.

Well, thanks for the clarification, anyway.


Regards

--
Aidan Whitehall <aidanwhitehall@fairbanks.co.uk>
Macromedia ColdFusion Developer
Fairbanks Environmental Ltd +44 (0)1695 51775

re: Delete trigger in SQL Server 7

Aidan Whitehall wrote:
[color=blue][color=green]
>>The main consideration is that you cannot have declarative referential
>>integrity in place when you perform cascading actions in triggers in SQL[/color]
>
> 7,
>
> Damn, damn, damn.
>
> Well, thanks for the clarification, anyway.[/color]

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
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

re: Delete trigger in SQL Server 7

> If I may request it ... would someone please confirm the above statement[color=blue]
> 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?[/color]

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" <damorgan@x.washington.edu> wrote in message
news:1073846407.832632@yasure...[color=blue]
> Aidan Whitehall wrote:
>[color=green][color=darkred]
> >>The main consideration is that you cannot have declarative referential
> >>integrity in place when you perform cascading actions in triggers in SQL[/color]
> >
> > 7,
> >
> > Damn, damn, damn.
> >
> > Well, thanks for the clarification, anyway.[/color]
>
> 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
> damorgan@x.washington.edu
> (replace 'x' with a 'u' to reply)
>[/color]

re: Delete trigger in SQL Server 7

Dan Guzman wrote:
[color=blue][color=green]
>>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?[/color]
>
>
> 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.[/color]

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
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

re: Delete trigger in SQL Server 7

> Thanks. I was a bit surprised that such basic functionality might not[color=blue]
> 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;[/color]

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

re: Delete trigger in SQL Server 7

Dan Guzman wrote:
[color=blue][color=green]
>>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;[/color]
>
>
> 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[/color]

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
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)