Solved

SQL Server/ADP/DMO Users Only

Posted on 2002-07-06
12
301 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
 
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
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: 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

930 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