Help with Triggers

  • I have to create 3 triggers for a table - INSERT, DELETE, and UPDATE.

    The triggers will be used to update the a column named num_rentals in a table named MOVIES each time a customer rental record has been added, deleted or inserted.

    Thanks.

    EDIT: completed triggers removed

    INSERT and DELETE triggers work. I now need to create an UPDATE trigger. See 3rd post.

  • cmorris1441 (4/20/2013)

    Here is the not working DELETE trigger:

    CREATE TRIGGER dbo.tr_num_rented_delete

    ON dbo.customer_rentals

    FOR DELETE

    AS

    BEGIN

    UPDATE m

    SET num_rentals = num_rentals - 1

    FROM dbo.movies AS m

    INNER JOIN inserted AS i

    ON m.movie_id = i.movie_id;

    END

    GO

    What am I doing wrong?

    Thanks.

    I haven't done any T-SQL myself but shouldn't you be calling the "deleted" MRT instead of "inserted"? In pl/sql I'd be calling :OLD instead of :NEW.

    Dird


    Dird

  • Dird (4/20/2013)


    I haven't done any T-SQL myself but shouldn't you be calling the "deleted" MRT instead of "inserted"? In pl/sql I'd be calling :OLD instead of :NEW.

    Dird

    Thanks that was it.

    Moving on to the UPDATE trigger...

    CREATE TRIGGER dbo.tr_num_rented_update

    ON dbo.customer_rentals

    FOR UPDATE

    AS

    BEGIN

    UPDATE m

    SET num_rentals = num_rentals

    FROM dbo.movies AS m

    INNER JOIN inserted AS i

    ON m.movie_id = i.movie_id;

    END

    GO

    Not really sure about this one... I set num_rentals = num_rentals because I'm not sure what it should be.

  • cmorris1441 (4/20/2013)

    Moving on to the UPDATE trigger...

    CREATE TRIGGER dbo.tr_num_rented_update

    ON dbo.customer_rentals

    FOR UPDATE

    AS

    BEGIN

    UPDATE m

    SET num_rentals = num_rentals

    FROM dbo.movies AS m

    INNER JOIN inserted AS i

    ON m.movie_id = i.movie_id;

    END

    GO

    Not really sure about this one... I set num_rentals = num_rentals because I'm not sure what it should be.

    It's hard to say without knowing more about the customer_rentals DDL. Would the rentals table even be updated or only insert/delete? Maybe something like this?

    CREATE TRIGGER dbo.tr_num_rented_update

    ON dbo.customer_rentals FOR UPDATE AS

    declare @prev INT;

    declare @curr INT;

    BEGIN

    select top 1 @prev=d.movie_id, @curr=i.movie_id from deleted d,inserted i;

    if(@prev!=@curr)

    BEGIN

    UPDATE m SET num_rentals = num_rentals -1

    FROM dbo.movies AS m where m.movie_id = @prev;

    UPDATE m SET num_rentals = num_rentals +1

    FROM dbo.movies AS m where m.movie_id = @curr;

    END

    END

    GO

    This is my first attempt to write TSQL (googled some syntax) so I'm not sure if it will compile but I guess you can understand my idea & fix the syntax. You should know that this will only work if updating 1 row (I guess? unless the trigger hits for every row separately even if you did an update with no where clause?)...but I guess updates here are unlikely to be on more than 1 row at a time? If you need to cater to muliple simultaenous row updates then you'll probably need to use 1/2 cursors.

    Dird


    Dird

  • Dird (4/20/2013)


    It's hard to say without knowing more about the customer_rentals DDL. Would the rentals table even be updated or only insert/delete? Maybe something like this?

    CREATE TRIGGER dbo.tr_num_rented_update

    ON dbo.customer_rentals FOR UPDATE AS

    DECLARE

    declare @prev INT;

    declare @curr INT;

    BEGIN

    select top 1 @prev=d.movie_id, @curr=i.movie_id from deleted d,inserted i;

    if(@prev!=@curr)

    BEGIN

    UPDATE m SET num_rentals = num_rentals -1

    FROM dbo.movies AS m where m.movie_id = @prev;

    UPDATE m SET num_rentals = num_rentals +1

    FROM dbo.movies AS m m.movie_id = @curr;

    commit;

    END

    END

    GO

    This is my first attempt to write TSQL (googled some syntax) so I'm not sure if it will compile but I guess you can understand my idea & fix the syntax. You should know that this will only work if updating 1 row (I guess? unless the trigger hits for every row separately even if you did an update with no where clause?)...but I guess updates here are unlikely to be on more than 1 row at a time? If you need to cater to muliple simultaenous row updates then you'll probably need to use 1/2 cursors.

    Dird

    Added a where clause and deleted 'DECLARE' and now it works!

    CREATE TRIGGER dbo.tr_num_rented_update

    ON dbo.customer_rentals FOR UPDATE AS

    declare @prev INT;

    declare @curr INT;

    BEGIN

    select top 1 @prev=d.movie_id, @curr=i.movie_id from deleted d,inserted i;

    if(@prev!=@curr)

    BEGIN

    UPDATE m SET num_rentals = num_rentals -1

    FROM dbo.movies AS m where m.movie_id = @prev;

    UPDATE m SET num_rentals = num_rentals +1

    FROM dbo.movies AS m where m.movie_id = @curr;

    END

    END

    GO

    Thanks a lot for the help.

  • Yeah I just tested it in my env 😮

    Also the commit shouldn't be within the transaction but I guess you copied it before I added it in~

    Dird


    Dird

  • Dird (4/20/2013)


    Yeah I just tested it in my env 😮

    Also the commit shouldn't be within the transaction but I guess you copied it before I added it in~

    Dird

    Should I add the commit to the trigger is it not necessary?

  • cmorris1441 (4/20/2013)


    Dird (4/20/2013)


    Yeah I just tested it in my env 😮

    Also the commit shouldn't be within the transaction but I guess you copied it before I added it in~

    Dird

    Should I add the commit to the trigger is it not necessary?

    I believe when the trigger ends it automatically commits. When I included a commit it threw an error message on my environment. I just included it from force of habit from Oracle; seems it isn't needed. You could always make an update then disconnect your session & see if the update is still there when you connect to your DB again~

    Dird


    Dird

  • cmorris1441 (4/20/2013)


    Dird (4/20/2013)


    Yeah I just tested it in my env 😮

    Also the commit shouldn't be within the transaction but I guess you copied it before I added it in~

    Dird

    Should I add the commit to the trigger is it not necessary?

    No. Not unless it matches a begin tran in the same trigger (ie a nested pseudo-transaction is what is being "commited").

    Normally triggers are something that happen inside transactions, not something that ends a transaction; the only common exception is a trigger that detects an error and rolls the transaction back. Commiting a real transaction in a trigger will usually call the batch from which the trigger was invoked to be aborted, although of course the transaction can not be rolled back, and that tends to be very confusing.

    Tom

  • SQL Server triggers only fire once per statement, no matter how many rows are INSERTed or UPDATEd (or DELETEd).

    Therefore, it's not safe to use variables to get column data (unless you use cursors, and nobody wants that).

    CREATE TRIGGER dbo.tr_num_rented_update

    ON dbo.customer_rentals

    AFTER UPDATE

    AS

    SET NOCOUNT ON;

    UPDATE m

    SET num_rentals = num_rentals + new.num_rentals - old.num_rentals

    FROM dbo.movies AS m

    INNER JOIN (

    SELECT movie_id, COUNT(*) AS num_rentals

    FROM inserted

    GROUP BY movie_id

    ) AS new ON

    new.movie_id = m.movie_id

    INNER JOIN (

    SELECT movie_id, COUNT(*) AS num_rentals

    FROM deleted

    GROUP BY movie_id

    ) AS old ON

    old.movie_id = m.movie_id

    GO

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • ScottPletcher (4/22/2013)


    unless you use cursors, and nobody wants that

    And you think that solution isn't implicitly using cursors?

    Edit: But yeah, it's a better way of doing it 😛

    Dird


    Dird

  • Dird (4/22/2013)


    ScottPletcher (4/22/2013)


    unless you use cursors, and nobody wants that

    And you think that solution isn't implicitly using cursors?

    Edit: But yeah, it's a better way of doing it 😛

    Dird

    I know my solution isn't using cursors, implicitly or otherwise.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Dird (4/22/2013)


    ScottPletcher (4/22/2013)


    unless you use cursors, and nobody wants that

    And you think that solution isn't implicitly using cursors?

    Edit: But yeah, it's a better way of doing it 😛

    Dird

    There's no cursor in his solution. Are there engine internal execution loops based on the internal objects of the plan? Ask Microsoft, but everything in a PC loops eventually. Is it a cursor? Most definately not.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Evil Kraig F (4/22/2013)


    There's no cursor in his solution.

    Is there any documentation/book which proves this? Oracle would be running implicit cursors here; I have a hard time believing sql server doesn't since so much of this rdbms is "coincidentally" similar

    I'm unable to test now (at work) but I'll run a similar kind of operation on a large table later & query sys.dm_exec_cursors

    Dird


    Dird

  • Dird (4/23/2013)


    Evil Kraig F (4/22/2013)


    There's no cursor in his solution.

    Is there any documentation/book which proves this? Oracle would be running implicit cursors here; I have a hard time believing sql server doesn't since so much of this rdbms is "coincidentally" similar

    I'm unable to test now (at work) but I'll run a similar kind of operation on a large table later & query sys.dm_exec_cursors

    Dird

    Oracle uses cursors (its version of a cursor) for all queries.

    Do you have any evidence whatsoever that SQL uses cursors for this type of code?

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply