Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Need help to create a trigger in SQL

Posted on 2011-02-25
2
Medium Priority
?
288 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:wademi
2 Comments
 
LVL 17

Expert Comment

by:dbaSQL
ID: 34985282
It would help if you could provide just a couple sample records from your Sales, Payment and Royalty tables.
0
 
LVL 7

Accepted Solution

by:
rmm2001 earned 2000 total points
ID: 34989770
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question