Help for triggers...

Question

Post reply

Help for triggers...

Bagath

SSCrazy

Points: 2409
More actions
October 24, 2006 at 2:31 am

#113980

Hi,

iam writing one trigger before insert and from that iam calling one batch file i need to pass the values i inserted into the table as parameters to batch file
il send u small script that i have written

CREATE TRIGGER testtrigger ON tab_name
FOR INSERT
AS exec xp_cmdshell 'C:\x\y.bat ex 13 57'

in place of 13 and 57 i have to pass the values that i inserted into the table.
any help would be useful to me

Thanks,
Baghat...

Viewing 4 posts - 1 through 3 (of 3 total)

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

ijaz SSCommitted Points: 1912 More actions · Answer 1

Hi,

When ever you modify or insert the data in the tables, SQL Server generate the two table ' INSERTED' , 'DELETED'...

you can get any field values from these tables you have inserted or deleted.

Declare @ID1 INT, @ID2 INT

SELECT @ID1 =ID1, @ID2 =ID2 FROM INSERTED.

you can use these variable any where you want according to your requirement.

I hope this will slove your problem.

Ijaz

cheers

Bagath SSCrazy Points: 2409 More actions · Answer 2

I have tried and successfully created the trigger......

Thank you very much Ijaz.... 🙂

Bagath

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

If I may add something. What happens if you have more than one row afted by the transaction that fired the trigger? Is it acceptable that some files will not be processed?

What happens if the command fails? >> The whole transaction fails so you lose the insert you just made.

The best way to do this is to place the rows to process in a staging table, have a job check for rows in that table every x minutes then have that job fire the external processes. That way you can always restart the job if something goes wrong.

Also if you need and immediate execution, you can always start the job from the trigger, but I rarely saw the need for that.