Solved

SQL Server/ADP/DMO Users Only

Posted on 2002-07-06
12
286 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__
Comment Utility
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
Comment Utility
Do you want to do it from the server-side or client side ?

regards
0
 
LVL 1

Author Comment

by:__Holly__
Comment Utility
id like to do it from the client side preferably.

0
 
LVL 1

Author Comment

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

Expert Comment

by:perove
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

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

Author Comment

by:__Holly__
Comment Utility
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__
Comment Utility
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
Comment Utility
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__
Comment Utility
i need to know how to make the trigger
0
 
LVL 9

Expert Comment

by:perove
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

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…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

762 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

7 Experts available now in Live!

Get 1:1 Help Now