Solved

Need help to create a trigger in SQL

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

Creating and Managing Databases with phpMyAdmin in cPanel.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

929 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now