Solved

SQL Server/ADP/DMO Users Only

Posted on 2002-07-06
12
312 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 400 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

815 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now