By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,362 Members | 1,337 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,362 IT Pros & Developers. It's quick & easy.

How are parameters passed in Triggers?

nitindel
P: 67
Hi All,
May i know how do we pass parameters in Triggers and if no parameters are passed then how they get fired...??

Thanks & Regards
Nitin Sharma
Jun 26 '08 #1
Share this Question
Share on Google+
13 Replies


debasisdas
Expert 5K+
P: 8,127
trigger do not require parameters. They execute automatically on the related database events.
Jun 27 '08 #2

ck9663
Expert 2.5K+
P: 2,878
Trigger does not dictate whether a trigger will fire or not. Triggers are based on events that happen to your table, not the parameters inside it.

-- CK
Jun 29 '08 #3

100+
P: 138
Trigger does not dictate whether a trigger will fire or not. Triggers are based on events that happen to your table, not the parameters inside it.

-- CK
hi,
what if i need to send some extra field to be inserted when a trigger gets fired.
i have a table and when a trigger is fired, the content of that table is inserted in the next table with the same table structure but with one more field 'modified_by'. how can i insert the field 'modified_by' when that trigger is fired. modified_by can be any name through which the application is logged in.
thanx
Jun 30 '08 #4

debasisdas
Expert 5K+
P: 8,127
yes you can do that .

but what is the problem ?
Jul 2 '08 #5

100+
P: 138
yes you can do that .

but what is the problem ?
Hi Debasisdas,

let me clear my confusion,
i have 2 tables tbl_1, tbl_2.
tbl_1 contains id,name and address. and tbl_2 contains old_id, old_name, old_address, new_id, new_name, new_address and modified_by.
i wrote one trigger which inserts the old values as well as the new values in tbl_2 when there is any updation or deletion in tbl_1.

now my problem is how can i insert 'modified_by' in tbl_2, which is the 'user name' in any other table through which the application is logged in. i want to insert the 'username' as the 'modified_by' in tbl_2. how can i pass the username in the trigger so that when ever it gets fired, it also insert the 'username' as 'modified_by' in tbl_2.

now i think it is clear.
with regards,
Jul 2 '08 #6

ck9663
Expert 2.5K+
P: 2,878
During insert to tbl2, identify the field names on the t-sql. Harcode the modified_by part. Something like,

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO (field1, field2, field3, modified_by) tbl2
  2. select field1, field2, field3, user_name() from inserted (or deleted whichever case).
  3.  
Happy coding...

-- CK
Jul 4 '08 #7

100+
P: 138
During insert to tbl2, identify the field names on the t-sql. Harcode the modified_by part. Something like,

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO (field1, field2, field3, modified_by) tbl2
  2. select field1, field2, field3, user_name() from inserted (or deleted whichever case).
  3.  
Happy coding...

-- CK
i think you don't understand what i am trying to say.

i want to pass the parameter at the run time, and it's the name of the person who has logged in. how can i know who has logged in by using the select statement? and the username comes from different table, not the same table
thanx
Jul 6 '08 #8

ck9663
Expert 2.5K+
P: 2,878
You're saying there's another table?

You got tbl_1 which is your transaction table. Anything happened there, you inserted the record in tbl_2. Where do you get the modified_by field?

-- CK
Jul 8 '08 #9

100+
P: 138
You're saying there's another table?

You got tbl_1 which is your transaction table. Anything happened there, you inserted the record in tbl_2. Where do you get the modified_by field?

-- CK
another table tbl_3 contains modified_by field.
Jul 13 '08 #10

ck9663
Expert 2.5K+
P: 2,878
You might just need to use JOIN


-- CK
Jul 13 '08 #11

100+
P: 138
You might just need to use JOIN


-- CK

how to join the tables in the trigger. i want to know that
i wrote a trigger
Expand|Select|Wrap|Line Numbers
  1.              on update or delete on tbl_1
  2.                  call trigger trigger_name
  3.  
how can i join tbl_1 and tbl_2 in the trigger
thanx
Jul 14 '08 #12

P: 1
I understand ur problem.You won't find this solution while doing google.You can pass parameters toriggers just like Procedures.It will work fine....

Vikrant Sharma(Ludhiana,india)
www.dotnetworkshop.in
iconsoftech,com
Sep 1 '08 #13

ck9663
Expert 2.5K+
P: 2,878
Could you post a sample code that allow passing of parameter to trigger, please? I was not aware that I can do that. I might be making things hard for a lot of things coz I never thought you can pass a parameter to a trigger.

Coolminded,
You can use JOIN like in any other t-sql. Here's the JOIN Fundamentals in BOL.

Good luck.

-- CK
Sep 2 '08 #14

Post your reply

Sign in to post your reply or Sign up for a free account.