?
Solved

SQL - Pass Value to Trigger

Posted on 2010-11-10
7
Medium Priority
?
613 Views
Last Modified: 2012-08-13
I do not know if this can be done.  I know with a stored procedure it can be accomplished.  I want to know if it can be accomplished with a Trigger.  Ok, lets say there are 3 tables - tableA, tableB, and tableC.   The result is to get data from tableA and tableB into tableC.

I call a stored procedure and in this stored procedure I pass in the values for TableA to perform an insert.  I am also inserting into TableB and getting the ID by SCOPE_IDENTITY.

So lets say I need the following fileds TableA.Name, TableA.Address, and TableC.ResultsID (these tables are not joined in anyway and the value of TableC.ResultsID will be obtained using SCOPE_IDENTITY).

The question is if I put a trigger on TableA to pass the data to TableC --  how do I get the TableC.ResultID data in the Trigger?

Hope this makes sense.
0
Comment
Question by:CipherIS
[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
  • 4
  • 2
7 Comments
 
LVL 9

Expert Comment

by:Philippe Damerval
ID: 34106584
The answer to your question of whether this can be done or not is yes, it can be done and is fairly easy. SCOPE_IDENTITY returns the last ID used in the current scope (current SP, UDF or batch).
The real question I think is what are you trying to achieve? Why are you copying data from one table to another? Once we determine what you want to achieve, we can best recommend how to do it, which may or may not involve launching inserts from inside triggers.

Let us know!

Philippe
0
 
LVL 1

Author Comment

by:CipherIS
ID: 34106726
I am building an alert system.  TableC will contain information for the Alerts.  

Certain fields in TableA (lets say its an employee table) need to be passed to TableC when an insert, update, or delete is performed.  

TableB is a log table.

The requirement is to pass the pkid of TableB to TableC while inserting TableA data into TableC.  

(hope I didn't confuse anyone).
0
 
LVL 1

Accepted Solution

by:
CipherIS earned 0 total points
ID: 34106974
i figured out how to do it.  thx.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 34110075
You can not pass parameter to Trigger. But you can access the deleted, inserted tables in it.
0
 
LVL 1

Author Comment

by:CipherIS
ID: 34313898
test
0
 
LVL 9

Expert Comment

by:Philippe Damerval
ID: 34313965
It's okay with me, although in the spirit of the experts-exchange community I wish the author would post a brief description of what solution worked for them. It is not clear whether the advice given by experts was useful or not.
Thanks!
0
 
LVL 1

Author Closing Comment

by:CipherIS
ID: 34341638
figured it out
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

762 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