Need help to create a trigger in SQL

Can some one help me with the script to create a trigger that updates my payment table when a record is inserted in the Sales table.

Please take a look at the attached schema.

The royaltyAmount in the payments table is calculated based on the rate in the Royalty table(default 10%)

The contactID to make the payment to is taken from the royalty table. CreateTableScripts.sql
wademiAsked:
Who is Participating?
 
rmm2001Connect With a Mentor Commented:
You'll want something like this. But I didn't see a script for the royalty table, so you'll have to add the join in to do that one. I just put the default 10%
CREATE TRIGGER TR_Sales ON dbo.Sales
AFTER INSERT

INSERT dbo.Payments (PublisherID, ISBN, ContactID, RoyaltyAmount)
SELECT p.PublisherID, s.ISBN, s.CustomerID, .1
FROM inserted s
JOIN dbo.Publishers p on p.ISBN = s.ISBN
JOIN dbo.Contacts c on c.ContactID = p.ContactID

GO

Open in new window

0
 
dbaSQLCommented:
It would help if you could provide just a couple sample records from your Sales, Payment and Royalty tables.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.