asked on

SQL Server/ADP/DMO Users Only

I want to create a loop in an Access Data Project that will create a standardized trigger for each table (in a specific SQL Server database) that will
1. keep track of who last changed the record
2. keep track of when the last record was changed.
3. record the previous information in a history table

I am not familiar with how to create a trigger programmatically.

Please help.

__Holly__

ASKER

points increased in order for more help.

im gonna start with the obvious--

ill make a VBA sub in the ADP--

Dim tbl as AccessObject

For each tbl in CurrentData.AllTables

docmd.RUNSQL "CREATE TRIGGER trigger" & tbl.Name & " FIELD autoUU ON UPDATE " & *************************
Next tbl

this part of it is pretty straight forward-- i just need to know how to create a trigger programmatically. (aka-- what I need to put in the SQL Statement)

ildc

Do you want to do it from the server-side or client side ?

regards

__Holly__

ASKER

id like to do it from the client side preferably.

__Holly__

ASKER

aka - a VB loop that will call SQL or DMO; etc

perove

You are *almost* there I think..

You should try to create the trigger manually first on one table to see what it'll look like, then re-bulid it into a generic string:

CREATE TRIGGER Brukere_Trigger1
ON dbo.Brukere FOR INSERT AS Update Table1 set ff='jj' WHERE pk = 1

which results in a function like this

Function create_triggers()

For Each tbl In CurrentData.AllTables
SQL = "CREATE TRIGGER " & tbl.name & "_trigger1 ON dbo." & tbl.name & " FOR INSERT AS Update Table1 set ff='jj' WHERE pk = 1"

DoCmd.RunSQL SQL
Next

End Function

perove

perove

one more thing..if you use SQL-DMO object the client will have to have the object installed. This is not always (a unerstatment) the case.

perove

__Holly__

ASKER

looks good man, thanks.. ill give that a shot and verify that works.

__Holly__

ASKER

so am i not allowed to update the inserted table?

id like to write all my code to update the inserted table (rather than having to code this for each table)

__Holly__

ASKER

ok guys.

i just need a little more help plz...

here is the sql for the sp 'spTrigger_AutoU':
Update I Set i.autoUU = suser_sname(), i.autoTU = GetDate()
FROM inserted I

here is the trigger creation DDL:
create trigger tr_autoUU_xtractBreakout ON xtractBreakout for update as BEGIN EXEC spTrigger_AutoU END

it keeps on telling me "Invalid Object Name 'Inserted'"

plz help!!!!

ASKER CERTIFIED SOLUTION

perove

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

__Holly__

ASKER

i need to know how to make the trigger

perove

Here is a samle of a trigger that put date/time in a table whenever it's changed

ALTER TRIGGER Test_table_Trigger1
ON dbo.Test_table
FOR INSERT, UPDATE

AS

UPdate Test_table
set Test_table.dateupdated =getdate()
from Test_table,inserted
where Test_table.nr=inserted.nr