Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need help to create a trigger in SQL

Posted on 2011-02-25
2
Medium Priority
?
285 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

721 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