Solved

Create Trigger Programmatically

Posted on 2002-07-06
20
773 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
 
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
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.

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

743 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

13 Experts available now in Live!

Get 1:1 Help Now