hi to all
I am bit new in store procedure and triggers and i am stuck in a problem
I am calling Store Procedure for inserting data into table1 and wana call trigger within the store procedure to insert part of data of table1 into table2

Can any expert help me....

Shailu,

You need not manually call a trigger. Go through the following steps for better understanding.

1. Create a INSERT trigger on Table1 which would insert the desired data into Table2
2. From your stored procedure just insert records into Table1
3. Automatically the insert trigger on Table1 would fire and take care of inserting the values into Table2 also.

Hope this helps! If you are stuck somewhere or if you need any examples do let me know.

MVP ASP/ASP.NET
http://vadivel.thinkingms.com

thanks for u r response.
But the problem is I don't have to always insert data into table2 when I am inserting data in to table1 except certain situations.

So I require a StoreProcedure and a Trigger for such situation.

Shailu,

You cannot fire a trigger yourself. Based on Insert/Update/Delete statement for a table .. it would fire automatically. That said, we can disable/enable a trigger based on our need.

So what i suggest is, within your SP when you don't want the trigger to fire (for some condition) just disable it.

For ex:
Alter table Table1 Disable Trigger testTrigger
go

Don't forget to re-enable the Trigger once your done :) Hope this helps!


Best Regards
Vadivel

MVP ASP/ASP.NET
http://vadivel.thinkingms.com

I suggest, not using a trigger in this case (if there are only a relatively few tables involved). Use the stored procedure to populate table 2 (by getting the last inserted ID from table 1 -- @@IDENTITY variable), you can also specify what last identity you want from what table by using the following syntax:

 IDENT_CURRENT('databasename.user.objectname')

Goodluck.

For fetching the identity value its always better to use Scope_identity. It returns the most recently inserted IDENTITY in the current scope. Its available only from Sql Server 2000.

On the other hand Ident_current would give you the most recent identity value for that table regardless of who inserted it.

Best Regards
Vadivel

MVP ASP/ASP.NET
http://vadivel.thinkingms.com