Solved

Create Trigger Programmatically

Posted on 2002-07-06
20
782 Views
Last Modified: 2012-05-04
I want to create a loop using SQLDMO 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. I would assume that i have 2 choices--

SQL
or
DMO

Please help.
0
Comment
Question by:__Holly__
  • 14
  • 3
  • 2
  • +1
20 Comments
 
LVL 18

Expert Comment

by:nigelrivett
ID: 7134670
I do this using sql.
You can probably find scripts that will do it but it's quite easy.

On www.nigelrivett.com (Generate Trigger) there is code to generate a trigger to log updates to fields of a table. Change this to log the whole record and call it for each table in the database to generate all the scripts.
0
 
LVL 1

Author Comment

by:__Holly__
ID: 7137030
thanks ill give it a shot.
0
 
LVL 1

Author Comment

by:__Holly__
ID: 7137571
ok i dig the code--

but im confused about what this specific trigger does?

it looks like it is one trigger that can tell which column is updated-- is this new with SQL2000????

________________________________
if update(i01)
select 'i01' , i01 from inserted
________________________________

please help and ill award pts...

________________________________
drop trigger tr_trigtest
go
create trigger tr_trigtest on trigtest for update
as
if update(i01)
select 'i01' , i01 from inserted
if update(i02)
select 'i02' , i02 from inserted
if update(i03)
select 'i03' , i03 from inserted
if update(i04)
select 'i04' , i04 from inserted


0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 5

Expert Comment

by:spcmnspff
ID: 7137906
The chalenging bit of info to know is from inside the trigger which table the current trigger is opertaing on. I tried this by selecting from SysLocks where the spid = @@spid with moderate success but nothing 100% reliable.  Nigelrivett, does your code do this?
0
 
LVL 1

Author Comment

by:__Holly__
ID: 7138000
i dont really honestly care about knowing which table it is working against.

what i want-- is to hardcode (that no matter what table this trigger is working against) that it will update the AutoUU = 'RASNW\aaron' and AutoDu = '7/8/2002'

i would love to only have to write this in one place- (because i am consistent in doing this on all tables (except of course SYS tables)
0
 
LVL 18

Accepted Solution

by:
nigelrivett earned 500 total points
ID: 7138088
To create the trigger for all tables
This will call the trigger creation SP for all tables
To create the script file use osql to call the SP.
(haven't run or compiled any of this)
will put the SP in another post.

create table #tables (name varchar(128))
insert #tables
select name from sysobjects where type = 'U'

(could use information_schema view and will need to get rid of the odd system table)

declare @name varchar(128)
select @name = ''

while @name < (select max(name) from #tables
begin
  select @name = min(name) from #tables where name > @name
  exec sp_createtrigger @name
end
0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 7138133

use the above script to run these for each table in the system
-- Create the trigger script
declare @table varchar(128)
declare @col varchar(128)
declare @s varchar(1000)

set nocount on
print 'create trigger tr_' + @table + ' on ' + @table + ' for update, delete'  
print 'as'
select @s= 'insert sav_' + @name + ' select *, getdate() from deleted
print @s
go

create the audit trail tables

set nocount on
declare @s varchar(128)
select @s = 'select *, UpdateDate = getdate() into sav_' + @name ' from ' + @name + ' where 1 = 0'
print @s
go


Use osql to save the output into files with the table name in the file name.
You can then use osql to execute all the files - or just concatenate them into a single file.
See www.nigelrivett.com Administering SQL Server Release Control for a method of concatenating files.

(You may have to put all the lines into a temp table then do a select on it to get the correct output for osql.
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7138152
Here's some vbscript that uses DMO to create a trigger on every non-system table in a given database.  Here, I've made the trigger simple, but if you had one table where you were inserting the values for System_User and GetDate, this could be easily modified:


Function Main()
     Dim objTrigger 'As Object
     Dim genObj         'As Object
     Dim db         'As Object
     Dim sql        'As Object
     Dim Cmd1       'As String
     Dim Cmd2       'As String

     Cmd1 = "Create Trigger trMyTrigger On "
     Cmd2 = " For Insert AS Select * form Inserted"

     Set sql = CreateObject("SQLDMO.SQLServer)
     Set db = CreateObject("SQLDMO.Database")        
     Set objTrigger = CreateObject("SQLDMO.Trigger")
   
     'Connect to the server
     sql.Connect server, "UID", "PWD"
     Set db = sql.Databases("dbname")  

     'Create Trigger for each non-system table in database
     For Each genObj In db.Tables
        If genObj.SystemObject = False Then
            db.ExecuteImmediate(Cmd1 + genObj.Name + Cmd2)
        End If
     Next
    Set objTrigger = Nothing
End Sub
0
 
LVL 1

Author Comment

by:__Holly__
ID: 7141344
re: Cmd1 = "Create Trigger trMyTrigger On "

dont i need to uniquely name the triggers?

aka- dont i need to cal it "trMyTrigger_" & genObj.name??

(its ok if you say no, i just thought that i would have to uniquely name these (for each table) even though I would _love_ to only have to maintain on trigger name for each table-

thanks..

0
 
LVL 1

Author Comment

by:__Holly__
ID: 7141833
nigel; im still evalling thanks for all your help...
0
 
LVL 1

Author Comment

by:__Holly__
ID: 7141834
nigel; im still evalling thanks for all your help...
0
 
LVL 1

Author Comment

by:__Holly__
ID: 7144895
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 1

Author Comment

by:__Holly__
ID: 7144896
btw, i increased points & i love you guys!!!
0
 
LVL 1

Author Comment

by:__Holly__
ID: 7144985
sorry am i not allowed to update the 'inserted' table??

0
 
LVL 1

Author Comment

by:__Holly__
ID: 7145180
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 1

Author Comment

by:__Holly__
ID: 7162869
nigel;

i havent gotten any sort of answer that helps--

so i was gonna re-eval your script-- but i cant see the server anymore..

sorry for the delay
0
 
LVL 1

Author Comment

by:__Holly__
ID: 7162875
aka the www.nigelrivett.com server
0
 
LVL 1

Author Comment

by:__Holly__
ID: 7165134
hey nigel;

i wanna re-eval your scripts-- i originally thoght that it was too complicated.. but i really wanna take a look at it again

Server Error
This server has encountered an internal error which prevents it from fulfilling your request. The most likely cause is a misconfiguration. Please ask the administrator to look for messages in the server's error log.
0
 
LVL 1

Author Comment

by:__Holly__
ID: 7179188
thanks--

i wish that i could see your scripts again--

0
 

Expert Comment

by:blockwood
ID: 7640792
LockwoodTech offers Auto-Audit an easy to set up tool to manage SQL Server database audits by automatically creating triggers.

http://www.lockwoodtech.com/index_aa.htm
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VBScript Write Column Headers 3 33
SQL Querying data from 3 tables, all with 1 common column 4 33
T-SQL: Do I need CLUSTERED here? 13 39
SQL Improvement  ( Speed) 14 26
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

808 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