How are parameters passed in Triggers?

[nitindel]

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

[debasisdas]

trigger do not require parameters. They execute automatically on the related database events.

[ck9663]

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

[coolminded]

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

[debasisdas]

yes you can do that .

but what is the problem ?

[coolminded]

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,

[ck9663]

During insert to tbl2, identify the field names on the t-sql. Harcode the modified_by part. Something like,

INSERT INTO (field1, field2, field3, modified_by) tbl2
select field1, field2, field3, user_name() from inserted (or deleted whichever case).
 

Happy coding...

-- CK

[coolminded]

During insert to tbl2, identify the field names on the t-sql. Harcode the modified_by part. Something like,

INSERT INTO (field1, field2, field3, modified_by) tbl2
select field1, field2, field3, user_name() from inserted (or deleted whichever case).
 

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

[ck9663]

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

[coolminded]

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.

[ck9663]

You might just need to use JOIN


-- CK

[coolminded]

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

             on update or delete on tbl_1
                 call trigger trigger_name
 

how can i join tbl_1 and tbl_2 in the trigger
thanx

[icon29]

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

[ck9663]

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