Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help with Triggers


Help with Triggers

Author
Message
cmorris1441
cmorris1441
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 8
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.
Dird
Dird
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 Visits: 771
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
cmorris1441
cmorris1441
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 8
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.
Dird
Dird
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 Visits: 771
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
cmorris1441
cmorris1441
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 8
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.
Dird
Dird
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 Visits: 771
Yeah I just tested it in my env :o
Also the commit shouldn't be within the transaction but I guess you copied it before I added it in~

Dird


Dird
cmorris1441
cmorris1441
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 8
Dird (4/20/2013)
Yeah I just tested it in my env :o
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?
Dird
Dird
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 Visits: 771
cmorris1441 (4/20/2013)
Dird (4/20/2013)
Yeah I just tested it in my env :o
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
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10707 Visits: 12008
cmorris1441 (4/20/2013)
Dird (4/20/2013)
Yeah I just tested it in my env :o
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

ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3944 Visits: 6680
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)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search