Trigger to Insert data into a table and execute Stored procedure based on if condition

Question

Trigger to Insert data into a table and execute Stored procedure based on if condition

xXShanXx

Right there with Babe

Points: 731
More actions
July 4, 2011 at 5:33 pm

#252954

Hi
I am new to these triggers, please help me out.
I have to create a trigger which creates a temp table and inserts data into it and based on some condition i need to execute the stored procedures.
I have a table called audit.
Account Amount
=================
10001 5
10003 10
10007 5
I need to create a trigger for this table when this table is inserted with an account and its amount.
I need to insert these values into a temp table.
and if amount = 5 then execute these 2 stored procedure.
if amount = 10 then execute there 2 stored procedures.
Create Trigger Triggername
on Tablename
For INSERT
AS
Create table #temptable1 (account int , amount int)
select account,amount from audit
insert into #temp (account,amount)
if (amount = 5)
begin
exec sp_storedprocedure1
exec sp_storedprocedure2
end
if (amount = 10)
begin
exec sp_storedprocedure3
exec sp_storedprocedure4
end
Please advise me on this structure.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

Take a look at the OUTPUT clause used in conjunction with an UPDATE to create your temporary table. Why create a trigger? Just put the additional code inline to a stored procedure that does the UPDATE.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

xXShanXx Right there with Babe Points: 731 More actions · Answer 2

Hi

Thanks for your reply

I am not familiar with Inline functions.

I have to run the stored procedures when ever the data gets inserted on audit table.

Regards

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

xXShanXx (7/4/2011)
Hi
Thanks for your reply
I am not familiar with Inline functions.
I have to run the stored procedures when ever the data gets inserted on audit table.
Regards

I'm not sure that an InLine function was the intent.

If you just include the logic that you were going to add to the trigger and place it in your Stored Procedure, that would be the better option in this case.

Edit: Unless this is a homework assignement where some Prof is telling you to do this.:-)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 4

WC is right. I'm sorry for using the term 'inline' in such a misleading way.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

xXShanXx Right there with Babe Points: 731 More actions · Answer 5

Hi

I have to call the stored procedures and i have to populate it on a temp table and my stored procedure will access the temp table

can any one advise on this whats needs to be done

thanks a lot in advance

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

xXShanXx (7/4/2011)
Hi
I have to call the stored procedures and i have to populate it on a temp table and my stored procedure will access the temp table
can any one advise on this whats needs to be done
thanks a lot in advance

I know that you already stated what you wanted to do using a trigger but could you briefly restate what you what to accomplish, step by step?

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

xXShanXx Right there with Babe Points: 731 More actions · Answer 7

My real senario is to when ever there is an insert in audit table , and based upon these values ie the amount field (5,10,20), I need to insert a set of values on a remote db table.

The SP will be the liked server insert statement procedures.

is it possibe on triggers to directly insert into the remote tables ?

Please advise

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

xXShanXx (7/5/2011)
is it possibe on triggers to directly insert into the remote tables ?

Yes you can but you have to enable cross database ownership chaining which is a security risk.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 9

xXShanXx (7/5/2011)
is it possibe on triggers to directly insert into the remote tables ?

Yes, but I am not sure what WC is referring to. I can't make a connection as to how cross-database ownership chaining might have something to to do with it.

That said, I would advise against using a Linked Server inside a trigger anyway. This can cause major problems in your database should the server being referenced by the Linked Server go down.

A better approach IMO for the trigger would be:

1) write the audit data to a local staging table

2) create a "sweeper process" in the form of a stored procedure that will copy data from the local staging table to the remote server using a Linked Server and remove it from the local staging table (EDIT: an SSIS package may be an option instead of a stored proc using a Linked Server)

3) schedule the "sweeper process" to run as often as is required

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato