Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 13 of 13
  1. #1
    New Coder
    Join Date
    Feb 2009
    Posts
    18
    Thanks
    1
    Thanked 0 Times in 0 Posts

    sql server 2005 - Delete Rows in a table based on deletion in another table.

    Hello,
    I have a table called employees and it has a list of employees. I want a trigger that says if an employee is deleted from the database then delete all the rows in another table (complaints) as long as they equal that person's id

    this is what i have written:
    CREATE TRIGGER EmployeeCleanUp ON employees
    AFTER DELETE
    AS
    DELETE FROM complaints
    WHERE complaints.EmpID = employee.EmpID

    I get the error "the multi-part identifier "employee.empid" could not be found"

    I am using sql server 2005. The only reason i could see this not working is becuase maybe the employee is already deleted, if so how do i fix this?

    This is for my senior project and i have not been able to find an answer.

    Thanks,
    Qua Sar

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,595
    Thanks
    78
    Thanked 4,387 Times in 4,352 Posts
    Well, it's a bad idea to use a trigger for this purpose, anyway.

    Instead, you should declare that the foreign key to primary key relationship in the complaints table is ON DELETE CASCADE, and then it would happen automatically!
    Read about CASCADE here:
    http://msdn.microsoft.com/en-us/libr...v=SQL.80).aspx

    But the answer to using the trigger is simple if you read the docs carefully:
    http://msdn.microsoft.com/en-us/libr...v=SQL.80).aspx

    Read the section that starts with[quote]
    A few special tables are used in CREATE TRIGGER statements:
    deleted and inserted are logical (conceptual) tables. ...
    [/code]
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    Feb 2009
    Posts
    18
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Why would it be a bad idea to use a trigger for this? I am just trying to create a trigger for my senior project to show I can do it. I may just use the cascade idea that you are talking about and try to find another reason to use a trigger, but I don't really see triggers usefullness for my database which is why i thought i would do this to show i can create and use trigger ideas.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,595
    Thanks
    78
    Thanked 4,387 Times in 4,352 Posts
    Okay, if that is your reason, go for the trigger.

    I think that in well designed systems triggers should be very very seldom used. I have one database here that has 278 stored procedures and one trigger, just as an example.

    Anyway, hopefully you realize that instead of specifying the actual table name of the delete record, you use the keyword deleted as a pseudo-table.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New Coder
    Join Date
    Feb 2009
    Posts
    18
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Yea I do realize that. Thank you. I think i am going to use the cascade feature for deleting linked structures though. But I do have another issue.. I now am trying to make a trigger to change a column in my table based on the change of another column in the table. For example in my books table (this is a psuedo-library system, I am making) I have NumOfCopies which is the number of copies I have. Now if that goes below 5 i want it to display in the Available column the word 'no' ... this way if too many books are taken out I can use PHP ( a website library system) to check that column to determine if the user can take out the book. I got it to work with just one check but I am trying to create one trigger to check if it's < or > 5. I am new to if statements in SQL and I am not sure this syntax is right... I am trying to figure it out now, but if someone could check my syntax I really would appreciate it.

    Code:
    ALTER TRIGGER AvailableBooks ON books
    AFTER Insert,Update
    AS
    BEGIN
      IF (SELECT NumOfCopies FROM INSERTED) < 5
      BEGIN
       UPDATE books
       SET "Available" = 'no'
       WHERE ISBN IN (SELECT ISBN FROM INSERTED)
       END;
    
       IF (SELECT NumOfCopies FROM INSERTED) < 5
       BEGIN
       UPDATE books
       SET "Available" = 'yes'
       WHERE ISBN IN (SELECT ISBN FROM INSERTED)
       END;
    END;

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,595
    Thanks
    78
    Thanked 4,387 Times in 4,352 Posts
    Well, I mainly code in MySQL and my SQL Server skills are rusty, but I *think* you want to make it simpler:
    Code:
    ALTER TRIGGER AvailableBooks ON books
    AFTER Insert,Update
    AS
    BEGIN
      UPDATE books 
      SET Available = ( CASE WHEN INSERTED.NumOfCopies < 5 THEN 'no' ELSE 'yes' END )
      WHERE ISBN = INSERTED.ISBN;
    END;
    No? Give it a try.

    Incidentally, I *still* wouldn't use a trigger for this. I think it's okay, as a "proof of concept". But in a real world system I'd probably use a Stored Procedure (or View) that joined the two tables and returned "Available" based on current conditions. I wouldn't have an actual field by that name.
    Last edited by Old Pedant; 04-12-2010 at 07:37 PM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    New Coder
    Join Date
    Feb 2009
    Posts
    18
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Okay. Do you know a way I can input these stored procedures in mysql through phpmyadmin i keep getting a #1064 error. I am using xampp lite. I am using this for the mysql/php and sql server 2005 to be able to compare the two different software mysql/sql server/phpmyadmin etc.

    And although I have gotten the stored procedures (and soon trigger) to work in sql server 2005 I am unsure how to get it to work in phpmyadmin which i want to do as then i can implement it with the website. Suggestions?



    EDIT: Just so you know with your code (which makes much more sense i agree) it gave me a multi-part identifier could not be bound error... so i changed it to.

    ALTER TRIGGER AvailableBooks ON books
    AFTER Insert,Update
    AS
    BEGIN
    UPDATE books
    SET Available = ( CASE WHEN (SELECT NumOfCopies FROM INSERTED) < 5 THEN 'no' ELSE 'yes' END )
    WHERE ISBN = (SELECT ISBN FROM INSERTED)
    END;

    It may be becuase my database is not the best. I don't have the best teacher (As I am still in college) and so I have not learned the ins and outs of datbases, which is why I decided to do my senior project on web design with a database running in. As well it could just be syntax differences between mysql and sql server.

    THanks again for your input sir and I hope I am not too annoying with these questions... I have been searching for awhile on the internet for my answers and have gotten nowhere as after i finish the parts of the project I am going to do I need to write a paper about what I did pros/cons what I didn't do that needs to be done etc... and I am running out of time on making the project the way i want it.
    Last edited by Qua Sar; 04-12-2010 at 08:09 PM.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,595
    Thanks
    78
    Thanked 4,387 Times in 4,352 Posts
    Well, I don't use phpMyAdmin. I just use the mysql command line tools, for the most part.

    But MySQL SP's are indeed a pain in the ***.

    Here's an example of how you could create one:
    Code:
    USE somedbname;
    DROP PROCEDURE IF EXISTS add_user;
    delimiter //
    CREATE PROCEDURE add_user
    -- Add a user and return the auto_number-generated user id
    (
       IN i_name VARCHAR(255),
       IN i_email VARCHAR(255)
    )
    BEGIN
       INSERT INTO site_users ( user_name, email )
       VALUES ( i_name, i_email );
       SELECT LAST_INSERT_ID();
    END
    //
    delimiter ;
    And good luck debugging MySQL SP's. I've got one that is about 3 pages long and it took me one full day just to debug the stupid thing. Mainly to find out that I had added an extra comma.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,595
    Thanks
    78
    Thanked 4,387 Times in 4,352 Posts
    And sorry about the syntax error w/ SQL Server. Like I said, rusty.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #10
    New Coder
    Join Date
    Feb 2009
    Posts
    18
    Thanks
    1
    Thanked 0 Times in 0 Posts
    No need to apologize, i understand things are different from software to software. I had assumed the general sql would be the same. Do you think it would be a good choice to maybe go to barnes and nobles and pick up a sql book specific for mysql since at the moment i have been going off the internet for sql and a learn sql in 10 minutes book or something for the basics? Or do you think google would probably be fine? I am by no means a "rock star" at sql and two stored procedures and a trigger is all i have left except for finishing some php dealing with those stored procedures.

    Thanks again for your help!

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,595
    Thanks
    78
    Thanked 4,387 Times in 4,352 Posts
    <shrug>I learned MySQL SP's and triggers just reading the MySQL docs. They aren't great, but they are adequate. Yes, I have since bought a book on advanced MySQL (mainly, how to get it to perform better), but I suppose it all depends on how well you learn from reading docs.

    I almost always learn new languages by starting from the documentation. What was the point in the creation of those docs if it wasn't to provide the most authoritative answers?

    Now, the MySQL docs do take some getting used to, but once you've bookmarked a dozen or two important sections you can get what you need from them.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Qua Sar (04-12-2010)

  • #12
    New Coder
    Join Date
    Feb 2009
    Posts
    18
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Alright. Thanks. I do know about the docs but they are kind of confusing... Tonight I will probably sit down and try to figure this out. I think it is harder because.. at least it seems with sql server 2005 creation of sp's and triggers and viewing them are simpler than in phpmyadmin which has no true support for sp and trigger viewing and everything.

    Thanks again for all your help!

  • #13
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,595
    Thanks
    78
    Thanked 4,387 Times in 4,352 Posts
    100% agreed about creating SPs and triggers in MySQL being hardware. The tools just aren't there. Might be some third-party tools, but nothing as handy as SQL Server tools, that I have found.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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