Solved

Need help to create a trigger in SQL

Posted on 2011-02-25
2
275 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 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

808 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