April 20, 2013 at 3:12 pm
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.
April 20, 2013 at 3:50 pm
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
April 20, 2013 at 4:50 pm
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.
April 20, 2013 at 5:08 pm
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
April 20, 2013 at 5:18 pm
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.
April 20, 2013 at 5:26 pm
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
April 20, 2013 at 5:34 pm
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?
April 20, 2013 at 5:46 pm
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
April 21, 2013 at 10:12 am
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
April 22, 2013 at 1:56 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 22, 2013 at 3:27 pm
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
April 22, 2013 at 3:32 pm
Dird (4/22/2013)
ScottPletcher (4/22/2013)
unless you use cursors, and nobody wants thatAnd 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 22, 2013 at 3:41 pm
Dird (4/22/2013)
ScottPletcher (4/22/2013)
unless you use cursors, and nobody wants thatAnd 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.
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
April 23, 2013 at 1:15 am
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
April 23, 2013 at 9:53 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply