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

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

Post by Pete » Fri, 30 May 2003 00:52:42



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

 
 
 

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

Post by Helmut W?s » Fri, 30 May 2003 09:37:38


if you use parameters you must use EXEC sp_executesql
Here you find good dokumentation how to do it:

http://www.algonet.se/%7Esommar/dynamic_sql.html

HTH,
Helmut



Quote:> 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

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

- Show quoted text -

Quote:

> 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


 
 
 

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

Post by Peter » Fri, 30 May 2003 10:09:50


Hello Helmut,

Thanx for the fast reply...however no luck though...I'm not using
parameters, just using a simple delete from query and select exists
subquery...I didn't find anything on the site that could help me...any
other suggestions?

Thanx!

Peter

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

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

Post by Helmut W?s » Sun, 01 Jun 2003 14:06:30


Hi Peter,

this happens if someone does not read the question carefully enough.
You are right - no parameters in use.
So i tried your example in my database and the error message
says, that rekursive procedures are not allowed in in an "instead of"
Trigger. This means, you cannot do a delete on the same table in an
"instead of delete" per exec().
I cannot explain it, but i think that a direct delete using the statement
does not start the "instead of" trigger again but exec() will do it.
So this would result in an endless loop.
I couldn't understand what your trigger should do. With some more
description maybe i can help you finding another solution.
But this does not mean that exec can't be used. Deleting something
in another table will work using exec()

bye,
Helmut



Quote:

> Hello Helmut,

> Thanx for the fast reply...however no luck though...I'm not using
> parameters, just using a simple delete from query and select exists
> subquery...I didn't find anything on the site that could help me...any
> other suggestions?

> Thanx!

> Peter

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

 
 
 

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

Post by Ted Bouskil » Sun, 01 Jun 2003 20:03:43


EXEC() or sp_executesql run in another execution plan.  They do not run with
the execution plan for the other statements in the trigger.   It's
equivalent to two people accessing the same table at the same time so yes it
will create a loop.


Quote:

> Hello Helmut,

> Thanx for the fast reply...however no luck though...I'm not using
> parameters, just using a simple delete from query and select exists
> subquery...I didn't find anything on the site that could help me...any
> other suggestions?

> Thanx!

> Peter

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

 
 
 

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

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

2. Solaris EEE - query runs on single node system and not on multi-node system

3. Using TRUNCATE TABLE" instead of DELETE TABLE

4. Query to show list of cubes, dimensions etc.

5. Delete trigger shows nothing in Deleted table

6. Build number, hotfix and service packs.

7. Can't delete table using Utility|delete

8. Forward Only Cursor ??

9. TRIGGER INSTEAD OF UPDATE and inserted and deleted tables

10. Delete trigger not updating rows using delete from

11. Deleting a temporary table using T-SQL

12. Deleted flag instead of delete?

13. INSTEAD OF DELETE and JOIN DELETED