Results 1 to 6 of 6

Thread: Triggers

  1. #1
    Join Date
    Jan 2004
    Posts
    18

    Smile Unanswered: Triggers

    I'm new to triggers, I would like to be able to delete a row from table "biotechnology" when a row from "employees" is deleted.

    biotechonlogy(ID,EmployeeID)
    Employees(ID, ...)

    Here is what I have,

    CREATE TRIGGER tr_Employees ON dbo.Employees
    FOR DELETE
    AS
    BEGIN
    DELETE Biotechnology
    WHERE EmployeeID = dbo.Employees.ID
    END

    I can't use this: dbo.Employees.ID

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    you should be doing

    CREATE TRIGGER tr_Employees ON dbo.Employees
    FOR DELETE
    AS
    BEGIN
    DELETE Biotechnology
    WHERE ID = deleted.ID
    END


    assuming that id in biotechnology is the id from employees table
    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
    Jan 2004
    Posts
    18
    The ID in Employees is the EmployeeID in Biotechnology. So I tried this instead and it still doesn't work. Seems like it doesn't recognize "deleted".

    CREATE TRIGGER tr_Employees ON dbo.Employees
    FOR DELETE
    AS
    BEGIN
    DELETE Biotechnology
    WHERE EmployeeID = deleted.ID
    END

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    my bad .. should have tested it first
    CREATE TRIGGER tr_Employees ON dbo.Employees
    FOR DELETE
    AS
    BEGIN
    DELETE Bio
    from Biotechnology Bio, Deleted del
    WHERE EmployeeID = del.ID
    END
    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
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You should do this through cascading relationional integrity instead of triggers, in my humble opinion. Brett is free to disagree!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jan 2004
    Posts
    18
    Thanks for the code Enigma, it works. I'll consider using cascading relational integrity instead of triggers.

Posting Permissions

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