SQL Triggers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Triggers

Author

Topic

deft321
Starting Member

2 Posts

Posted - 2014-11-19 : 05:11:22

Hi, this is my first post on here. I am trying to learn about triggers, I know how to do really basic ones, but I can't wrap my head around this. I have two tables Services(master) and Sales.
Services(ServiceID,ServiceCost,SalesTotal)
Sales(TransactionID,TransactionDate,Amount,ServiceID)

I am trying to write trigger for Update,Delete,Insert. When ever you enter a new sale in the Sales table, the SalesTotal will get updated in the Services table according to ServiceID.

ex: INSERT INTO Sales(TransactionID,TransactionDate,Amount,ServiceID)
VALUES ('16','2014-11-19','50','101');

So if the SalesTotal for TransactionID '101' was 1000, after the insert it would be 1050.

I think I have to use join tables, but I am currently stumped.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-19 : 09:07:02

something like this:


CREATE TRIGGER foo ON san.[host] FOR INSERT
AS
BEGIN
IF @@ROWCOUNT = 0 RETURN;
MERGE INTO [Services] svc
USING inserted ins
   ON svc.ServiceID = inserted.ServiceID
WHEN MATCHED THEN
	UPDATE SET SalesTotal += inserted.amount
WHEN NOT MATCHED THEN
    INSERT (amount) VALUES
		(inserted.amount)
;
END

This basically says, if there is a matching row in the Services table (by ServiceID) update it by adding the new amount; If there is no matching row, add one.

Not sure if the last bit is what you need.

deft321
Starting Member

2 Posts

Posted - 2014-11-19 : 11:59:29

quote:
Originally posted by gbritton

something like this:

CREATE TRIGGER foo ON san.[host] FOR INSERT
AS
BEGIN
IF @@ROWCOUNT = 0 RETURN;
MERGE INTO [Services] svc
USING inserted ins
ON svc.ServiceID = inserted.ServiceID
WHEN MATCHED THEN
UPDATE SET SalesTotal += inserted.amount
WHEN NOT MATCHED THEN
INSERT (amount) VALUES
(inserted.amount)
;
END

This basically says, if there is a matching row in the Services table (by ServiceID) update it by adding the new amount; If there is no matching row, add one.

Not sure if the last bit is what you need.

Thanks for replying, but sorry I don't understand the code it's a bit to advanced for me.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-19 : 12:44:27

well, then, what are looking for? Triggers are a bit advanced, it is true.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources