Procedure/Trigger Question

[AshleyRickards]

We have a SQL db that I want to write a procedure for. What I want it to do is set up a trigger that fires everytime someone makes a change to certain tables. The trigger should run a stored procedure or package that kicks the changed records out to a text file that will be loaded into our Active Directory system to update the changes there.

Any Ideas? Thanks.
Ashley L Rickards
SQL DBA

 

[Saama5]

reate trigger abc on tblname For Update
as
exec master.dbo.xp_sp1 "value1",value2....

This is how you can write trigger on table for insert,update or delete. Exec Extended stored procedure or stored procedure from your trigger.

 

[AshleyRickards]

Thanks. But how would I first write those records to a table and then execute the SP? Ashley L Rickards
SQL DBA

 

[tlbroadbent]

Simple case: Insert the new rows into a transaction table.

Create Trigger Trg_UpdMyTable On MyTable
For Update, Insert

As

--Insert from the virtual table inserted
--which is only available in a trigger.

Insert TransactionTable
Select * From Inserted

--Execute the SP
Exec MyStoredProc

I actually prefer to execute the stored procedure from a scheduled job especially if it takes any significant amount of time. This allows the user transaction to complete quickly. The Active directory update may be delayed slightly.

Another advantage of this is that the user does not require permissions to execute the stored procedure. Permissions can be limited to system administrators. This will prevent unauthorized updates to the Active Directory. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[AshleyRickards]

Thanks Terry! That will get us started. Ashley L Rickards
SQL DBA