No INSTEAD OF DELETE triggers for tables with ON DELETE CASCADE constraints

No INSTEAD OF DELETE triggers for tables with ON DELETE CASCADE constraints

Post by Johannes Lebe » Tue, 29 Apr 2003 18:17:21



Hello,

I tried to port a database schema from DB2 to SQL Server 2000.

SQL Server cannot handle two foreign key ON DELETE CASCADE constraints of
one table that both refer to the same second table. It says these
constraints cause a cyclic reference. Therefore, I changed these constraints
to ON DELETE NO ACTION constraints and added INSTEAD OF DELETE triggers to
the second/referenced table. Unfortunately, this is not allowed by SQL
Server. There might be a solution as I described below. But I do not like it
taht much!

==============================
Original:
==============================
CREATE TABLE TABLE1 (
    OID BIGINT NOT NULL,
    SRC BIGINT NOT NULL,
    TGT BIGINT NOT NULL,
    CONSTRAINT PK930 PRIMARY KEY (OID),
    CONSTRAINT FK930A FOREIGN KEY (SRC) REFERENCES PORT_INST (OID) ON DELETE
CASCADE,
    CONSTRAINT FK930B FOREIGN KEY (TGT) REFERENCES PORT_INST (OID) ON DELETE
CASCADE
);

CREATE TABLE TABLE2 (
    OID BIGINT NOT NULL,
    TABLE3_OID BIGINT NOT NULL,
    CONSTRAINT PK300 PRIMARY KEY (OID),
    CONSTRAINT FK300A FOREIGN KEY (TABLE3_OID) REFERENCES TABLE3 (OID) ON
DELETE CASCADE,
);

==============================
Port for SQL Server:
==============================
CREATE TABLE TABLE1 (
    OID BIGINT NOT NULL,
    SRC BIGINT NOT NULL,
    TGT BIGINT NOT NULL,
    CONSTRAINT PK930 PRIMARY KEY (OID),
    CONSTRAINT FK930A FOREIGN KEY (SRC) REFERENCES PORT_INST (OID) ON NO
ACTION,
    CONSTRAINT FK930B FOREIGN KEY (TGT) REFERENCES PORT_INST (OID) ON NO
ACTION
);

CREATE TABLE TABLE2 (
    OID BIGINT NOT NULL,
    TABLE3_OID BIGINT NOT NULL,
    CONSTRAINT PK300 PRIMARY KEY (OID),
    CONSTRAINT FK300A FOREIGN KEY (TABLE3_OID) REFERENCES TABLE3 (OID) ON
DELETE CASCADE,
);

CREATE TRIGGER TR300
    ON TABLE2 INSTEAD OF DELETE
    AS BEGIN
        DELETE FROM TABLE2 WHERE TABLE2.SRC IN (SELECT OID FROM deleted)
        DELETE FROM TABLE2 WHERE TABLE2.TGT IN (SELECT OID FROM deleted)
        DELETE FROM TABLE1 WHERE TABLE1.OID IN (SELECT OID FROM deleted)
    END;

============================
Error message:
============================
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC][SQLServer]Cannot CREATE INSTEAD OF DELETE or UPDATE TRIGGER 'TR300' on
table 'TABLE2' because the table has a FOREIGN KEY with cascaded DELETE or
UPDATE.
_______________________________

The only way I see is changing constraint FK300A to NO ACTION and define a
trigger for table TABLE3. But actually, I do not like this way...
Do you know a smarter way?

Johannes

 
 
 

No INSTEAD OF DELETE triggers for tables with ON DELETE CASCADE constraints

Post by Uri Diman » Tue, 29 Apr 2003 19:47:32


Johannes
I really don't know about your bussines requrements
As you can see I separated into two tables related "child" to the parents
Can you explain what do you need to keep to columns as FK related to the
same PK on other table?

create table parents
(
 col1 int not null primary key,
 col2 varchar(50) not null
)

create table child1
(
 col1 int not null primary key,
 col2 int not null references parents(col1) on delete cascade,
 col3 varchar(50) not null
)
create table child2
(
 col1 int not null primary key,
 col2 int not null references parents(col1) on delete cascade,
 col3 varchar(50) not null
)

insert into parents values (1,'a')
insert into parents values (2,'b')

insert into child1 values (1,1,'a')
insert into child1 values (2,1,'a')
insert into child1 values (3,2,'b')

insert into child2 values (1,1,'a')
insert into child2 values (2,2,'b')
insert into child2 values (3,2,'b')

delete parents where col1=1
select * from child1
select * from child2

drop table parents
drop table child1
drop table child2


Quote:> Hello,

> I tried to port a database schema from DB2 to SQL Server 2000.

> SQL Server cannot handle two foreign key ON DELETE CASCADE constraints of
> one table that both refer to the same second table. It says these
> constraints cause a cyclic reference. Therefore, I changed these
constraints
> to ON DELETE NO ACTION constraints and added INSTEAD OF DELETE triggers to
> the second/referenced table. Unfortunately, this is not allowed by SQL
> Server. There might be a solution as I described below. But I do not like
it
> taht much!

> ==============================
> Original:
> ==============================
> CREATE TABLE TABLE1 (
>     OID BIGINT NOT NULL,
>     SRC BIGINT NOT NULL,
>     TGT BIGINT NOT NULL,
>     CONSTRAINT PK930 PRIMARY KEY (OID),
>     CONSTRAINT FK930A FOREIGN KEY (SRC) REFERENCES PORT_INST (OID) ON
DELETE
> CASCADE,
>     CONSTRAINT FK930B FOREIGN KEY (TGT) REFERENCES PORT_INST (OID) ON
DELETE
> CASCADE
> );

> CREATE TABLE TABLE2 (
>     OID BIGINT NOT NULL,
>     TABLE3_OID BIGINT NOT NULL,
>     CONSTRAINT PK300 PRIMARY KEY (OID),
>     CONSTRAINT FK300A FOREIGN KEY (TABLE3_OID) REFERENCES TABLE3 (OID) ON
> DELETE CASCADE,
> );

> ==============================
> Port for SQL Server:
> ==============================
> CREATE TABLE TABLE1 (
>     OID BIGINT NOT NULL,
>     SRC BIGINT NOT NULL,
>     TGT BIGINT NOT NULL,
>     CONSTRAINT PK930 PRIMARY KEY (OID),
>     CONSTRAINT FK930A FOREIGN KEY (SRC) REFERENCES PORT_INST (OID) ON NO
> ACTION,
>     CONSTRAINT FK930B FOREIGN KEY (TGT) REFERENCES PORT_INST (OID) ON NO
> ACTION
> );

> CREATE TABLE TABLE2 (
>     OID BIGINT NOT NULL,
>     TABLE3_OID BIGINT NOT NULL,
>     CONSTRAINT PK300 PRIMARY KEY (OID),
>     CONSTRAINT FK300A FOREIGN KEY (TABLE3_OID) REFERENCES TABLE3 (OID) ON
> DELETE CASCADE,
> );

> CREATE TRIGGER TR300
>     ON TABLE2 INSTEAD OF DELETE
>     AS BEGIN
>         DELETE FROM TABLE2 WHERE TABLE2.SRC IN (SELECT OID FROM deleted)
>         DELETE FROM TABLE2 WHERE TABLE2.TGT IN (SELECT OID FROM deleted)
>         DELETE FROM TABLE1 WHERE TABLE1.OID IN (SELECT OID FROM deleted)
>     END;

> ============================
> Error message:
> ============================
> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
> JDBC][SQLServer]Cannot CREATE INSTEAD OF DELETE or UPDATE TRIGGER 'TR300'
on
> table 'TABLE2' because the table has a FOREIGN KEY with cascaded DELETE or
> UPDATE.
> _______________________________

> The only way I see is changing constraint FK300A to NO ACTION and define a
> trigger for table TABLE3. But actually, I do not like this way...
> Do you know a smarter way?

> Johannes


 
 
 

No INSTEAD OF DELETE triggers for tables with ON DELETE CASCADE constraints

Post by Johannes Lebe » Tue, 29 Apr 2003 18:57:20


Uri,

thank you, but unfortunately it is not that easy. The scenario is more
complex. There is one object (TABLE1), that connects objects of another type
(TABLE2). One source and one target. Because they are of the same type and
even because they might be exchanged (source to target, target to source),
they should be stored in the very same table.

Johannes


> Johannes
> I really don't know about your bussines requrements
> As you can see I separated into two tables related "child" to the parents
> Can you explain what do you need to keep to columns as FK related to the
> same PK on other table?

> create table parents
> (
>  col1 int not null primary key,
>  col2 varchar(50) not null
> )

> create table child1
> (
>  col1 int not null primary key,
>  col2 int not null references parents(col1) on delete cascade,
>  col3 varchar(50) not null
> )
> create table child2
> (
>  col1 int not null primary key,
>  col2 int not null references parents(col1) on delete cascade,
>  col3 varchar(50) not null
> )

> insert into parents values (1,'a')
> insert into parents values (2,'b')

> insert into child1 values (1,1,'a')
> insert into child1 values (2,1,'a')
> insert into child1 values (3,2,'b')

> insert into child2 values (1,1,'a')
> insert into child2 values (2,2,'b')
> insert into child2 values (3,2,'b')

> delete parents where col1=1
> select * from child1
> select * from child2

> drop table parents
> drop table child1
> drop table child2



> > Hello,

> > I tried to port a database schema from DB2 to SQL Server 2000.

> > SQL Server cannot handle two foreign key ON DELETE CASCADE constraints
of
> > one table that both refer to the same second table. It says these
> > constraints cause a cyclic reference. Therefore, I changed these
> constraints
> > to ON DELETE NO ACTION constraints and added INSTEAD OF DELETE triggers
to
> > the second/referenced table. Unfortunately, this is not allowed by SQL
> > Server. There might be a solution as I described below. But I do not
like
> it
> > taht much!

> > ==============================
> > Original:
> > ==============================
> > CREATE TABLE TABLE1 (
> >     OID BIGINT NOT NULL,
> >     SRC BIGINT NOT NULL,
> >     TGT BIGINT NOT NULL,
> >     CONSTRAINT PK930 PRIMARY KEY (OID),
> >     CONSTRAINT FK930A FOREIGN KEY (SRC) REFERENCES PORT_INST (OID) ON
> DELETE
> > CASCADE,
> >     CONSTRAINT FK930B FOREIGN KEY (TGT) REFERENCES PORT_INST (OID) ON
> DELETE
> > CASCADE
> > );

> > CREATE TABLE TABLE2 (
> >     OID BIGINT NOT NULL,
> >     TABLE3_OID BIGINT NOT NULL,
> >     CONSTRAINT PK300 PRIMARY KEY (OID),
> >     CONSTRAINT FK300A FOREIGN KEY (TABLE3_OID) REFERENCES TABLE3 (OID)
ON
> > DELETE CASCADE,
> > );

> > ==============================
> > Port for SQL Server:
> > ==============================
> > CREATE TABLE TABLE1 (
> >     OID BIGINT NOT NULL,
> >     SRC BIGINT NOT NULL,
> >     TGT BIGINT NOT NULL,
> >     CONSTRAINT PK930 PRIMARY KEY (OID),
> >     CONSTRAINT FK930A FOREIGN KEY (SRC) REFERENCES PORT_INST (OID) ON NO
> > ACTION,
> >     CONSTRAINT FK930B FOREIGN KEY (TGT) REFERENCES PORT_INST (OID) ON NO
> > ACTION
> > );

> > CREATE TABLE TABLE2 (
> >     OID BIGINT NOT NULL,
> >     TABLE3_OID BIGINT NOT NULL,
> >     CONSTRAINT PK300 PRIMARY KEY (OID),
> >     CONSTRAINT FK300A FOREIGN KEY (TABLE3_OID) REFERENCES TABLE3 (OID)
ON
> > DELETE CASCADE,
> > );

> > CREATE TRIGGER TR300
> >     ON TABLE2 INSTEAD OF DELETE
> >     AS BEGIN
> >         DELETE FROM TABLE2 WHERE TABLE2.SRC IN (SELECT OID FROM deleted)
> >         DELETE FROM TABLE2 WHERE TABLE2.TGT IN (SELECT OID FROM deleted)
> >         DELETE FROM TABLE1 WHERE TABLE1.OID IN (SELECT OID FROM deleted)
> >     END;

> > ============================
> > Error message:
> > ============================
> > java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
> > JDBC][SQLServer]Cannot CREATE INSTEAD OF DELETE or UPDATE TRIGGER
'TR300'
> on
> > table 'TABLE2' because the table has a FOREIGN KEY with cascaded DELETE
or
> > UPDATE.
> > _______________________________

> > The only way I see is changing constraint FK300A to NO ACTION and define
a
> > trigger for table TABLE3. But actually, I do not like this way...
> > Do you know a smarter way?

> > Johannes

 
 
 

1. instead of delete trigger delete data from table using execute and temporary table for deleted

Hello,

SQL Server 2k has got me confused...below the instead of trigger that
causes all my problems...below the trigger the explanation of my
problem

===============================================================================
create trigger [triggerConcurrenten] on [dbo].[concurrenten]
instead of delete
as
begin
        -- ***** Declare local vars




        begin transaction

        -- ***** Initialize



        select deleted.* into #deleted from deleted

        /*
        Delete data from concurrenten table using the below sql
statement
        works perfectly.
        =============
        delete
        from concurrenten
        where exists(
        select * from #deleted d
        where concurrenten.[id] = d.[id])
        */

        /* Delete using the execute function fails! */







        begin
                raiserror('[triggerConcurrenten]: Error occured!', 16, 1)
                rollback transaction
        end

        commit transaction      
end

===============================================================================

In the trigger I've tried a 'direct' sql statement to delete data from
the concurrenten table. No problem at all...but when I use the execute
function the exact same delete statement fails...what am I doing
wrong?

Thanx in advance for the answer!

Best regards,

Peter

2. Grant access to a unique IP

3. CASCADE Delete Vs INSTEAD Of Trigger ??

4. HELP: SQL*Loader Error

5. Using triggers or RI constraints to cascading updates / deletes

6. TOAST and TEXT

7. delete trigger or cascade delete, which one??

8. Update Error

9. ON DELETE CASCADE - does it fire delete trigger?

10. HELP! Creating delete trigger for cascade delete

11. Delete Cascade & Delete Triggers

12. delete tables with cascade constraint

13. TRIGGER INSTEAD OF UPDATE and inserted and deleted tables