Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Server/ADP/DMO Users Only

Posted on 2002-07-06
12
Medium Priority
?
370 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:__Holly__
  • 7
  • 4
12 Comments
 
LVL 1

Author Comment

by:__Holly__
ID: 7134646
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)
0
 
LVL 7

Expert Comment

by:ildc
ID: 7135096
Do you want to do it from the server-side or client side ?

regards
0
 
LVL 1

Author Comment

by:__Holly__
ID: 7137019
id like to do it from the client side preferably.

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:__Holly__
ID: 7137022
aka - a VB loop that will call SQL or DMO; etc
0
 
LVL 9

Expert Comment

by:perove
ID: 7137929
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

0
 
LVL 9

Expert Comment

by:perove
ID: 7137934
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
0
 
LVL 1

Author Comment

by:__Holly__
ID: 7138689
looks good man, thanks.. ill give that a shot and verify that works.
0
 
LVL 1

Author Comment

by:__Holly__
ID: 7144990
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)
0
 
LVL 1

Author Comment

by:__Holly__
ID: 7145182
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!!!!
0
 
LVL 9

Accepted Solution

by:
perove earned 1200 total points
ID: 7145452
First, you cannot update the insert table. That 's just how it is..


Second.
Is your goal to write just one trigger that will work on every table? I'm not quite follow you. Your first prbl was how to create a trigger from VBA, now is the Q more on the syntax of the T-SQL if the trigger??

perove


0
 
LVL 1

Author Comment

by:__Holly__
ID: 7160492
i need to know how to make the trigger
0
 
LVL 9

Expert Comment

by:perove
ID: 7161343
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


0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question