Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Help with Triggers Expand / Collapse
Author
Message
Posted Saturday, April 20, 2013 3:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, April 20, 2013 6:33 PM
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.
Post #1444746
Posted Saturday, April 20, 2013 3:50 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:20 AM
Points: 196, Visits: 650
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 // Junior DBA
11g OCA
10.5 newbie
Post #1444749
Posted Saturday, April 20, 2013 4:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, April 20, 2013 6:33 PM
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.
Post #1444756
Posted Saturday, April 20, 2013 5:08 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:20 AM
Points: 196, Visits: 650
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 // Junior DBA
11g OCA
10.5 newbie
Post #1444758
Posted Saturday, April 20, 2013 5:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, April 20, 2013 6:33 PM
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.
Post #1444761
Posted Saturday, April 20, 2013 5:26 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:20 AM
Points: 196, Visits: 650
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 // Junior DBA
11g OCA
10.5 newbie
Post #1444762
Posted Saturday, April 20, 2013 5:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, April 20, 2013 6:33 PM
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?
Post #1444764
Posted Saturday, April 20, 2013 5:46 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:20 AM
Points: 196, Visits: 650
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 // Junior DBA
11g OCA
10.5 newbie
Post #1444766
Posted Sunday, April 21, 2013 10:12 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 9:14 AM
Points: 8,679, Visits: 9,205
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
Post #1444809
Posted Monday, April 22, 2013 1:56 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:28 PM
Points: 2,027, Visits: 3,023
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1445153
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse