Solved

SQL Server/ADP/DMO Users Only

Posted on 2002-07-06
12
334 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__
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

733 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