passing parameter to trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

passing parameter to trigger

Author

Topic

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2007-01-10 : 23:44:40

How do i pass parameter to trigger.iam using trigger to delete from multiple tables here is the code

Create trigger DeletePartyName on PartyMaster_Gen
(@PartyName varchar(50))

for delete

as
DELETE FROM PartyMaster_Address WHERE PartyName=@PartyName
DELETE FROM PartyMaster_DEPB WHERE Party_Name=@PartyName
DELETE FROM PartyMaster_Gen WHERE Party_Name=@PartyName

I want to pass @partyname parameter to this query. how do i do it.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-11 : 00:42:22

you can't do this. think about it: who would pass the param?

www.elsasoft.org

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-11 : 01:49:44

Rewrite TRIGGER as a STORED PROCEDURE...
With your code above, you are triggering yourself! Trigger is on partymaster_gen, and the trigger deletes from the same table...

Or make the TRIGGER auto sensitive (remove data for all three tables where partyname is involved)

Create trigger DeletePartyName on PartyMaster_Gen
for delete

as
DELETE FROM PartyMaster_Address as p where exists (select null from deleted as d where d.partyname = p.partyname)
DELETE FROM PartyMaster_DEPB as p where exists (select null from deleted as d where d.partyname = p.party_name)

And yuo fire the trigger by executing

delete from partymaster_gen where party_name = 'Some party name here'

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2007-01-11 : 11:24:55

Is partyname in the table that's being affected

Plus you are thinking in terms of a single row, a trigger can affect multiple rows at a time

Think set based

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

hfloresve
Starting Member

2 Posts

Posted - 2012-09-14 : 14:45:00

So late, but for new ppl who have this question, i describe my solution:

I will describe sample with different tables...

Table1
Name: Components
Component_iD (Pk)
Component (name component)

table 2
Name: History
History_iD (Pk)
Component_iD
Component
ModifiedBy
Date... etc

1.- Create trigger on table 1 to write in my historytable after insert/update
2.- Create a stored procedure to insert on table 1, and input parameter "@username"
3.- On my stored procedure i get @@Identity from generated record (after insert, when update just take component_id)
4.- Update ModifiedBy field on table 2 where component_iD = @@Identity (or component_iD it depends from case, and setting value in @username

Well, this is a fast solution to this problem, i hope to help someone :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources