Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    33

    Question Unanswered: trigger for delete problem

    Hi,
    I have two tables:
    table A with key name, version
    table B with key id and foreing key A.name,A.version.

    The realtion A to B is 1:n

    I use following trigger to keep data integrity (when record in A deleted, all records with corresponding foreing key should be deleted from table B):

    CREATE TRIGGER trDelA ON A
    FOR DELETE
    AS
    DECLARE @n int , @ver int
    SELECT @n = name , @ver = version
    FROM deleted
    DELETE B
    FROM B,A WHERE
    @n=B.name AND
    @ver=B.version

    This trigger works fine, except the case that it doesn't fire when last record deleted from A.
    why does it happens? how can I fix it?

    Thank you

    p.s. i'm using sql server 2000
    Thank you
    Yulia

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    One thing is the trigger wont work when multiple deletes occur through one statement


    your trigger needs to be
    Code:
    CREATE TRIGGER trDelA ON A
    FOR DELETE 
    AS
    begin
    DELETE 
    	B
    FROM 
    	B,
    	DELETED D 
    WHERE 
    	D.name=B.name AND
    	D.version=B.version
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Nov 2003
    Posts
    33
    Originally posted by Enigma
    One thing is the trigger wont work when multiple deletes occur through one statement


    your trigger needs to be
    Code:
    CREATE TRIGGER trDelA ON A
    FOR DELETE 
    AS
    begin
    DELETE 
    	B
    FROM 
    	B,
    	DELETED D 
    WHERE 
    	D.name=B.name AND
    	D.version=B.version
    Hi,
    what does statementt "begin" means? the trigger has syntax error with it...
    Thank you
    Yulia

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Code:
    CREATE TRIGGER trDelA ON A
    FOR DELETE 
    AS
    begin
    DELETE 
    	B
    FROM 
    	B,
    	DELETED D 
    WHERE 
    	D.name=B.name AND
    	D.version=B.version
    end
    begin and end are keywords in sql to signify blocks of code ...
    just like you use { ... } in C
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Nov 2003
    Posts
    33

    Thumbs up thanks

    Thank you very much, it works!
    Thank you
    Yulia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •