Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 830
  • Last Modified:

Create Trigger Programmatically

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
__Holly__
Asked:
__Holly__
  • 14
  • 3
  • 2
  • +1
1 Solution
 
nigelrivettCommented:
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
 
__Holly__Author Commented:
thanks ill give it a shot.
0
 
__Holly__Author Commented:
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
spcmnspffCommented:
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
 
__Holly__Author Commented:
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
 
nigelrivettCommented:
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
 
nigelrivettCommented:

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
 
spcmnspffCommented:
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
 
__Holly__Author Commented:
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
 
__Holly__Author Commented:
nigel; im still evalling thanks for all your help...
0
 
__Holly__Author Commented:
nigel; im still evalling thanks for all your help...
0
 
__Holly__Author Commented:
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
 
__Holly__Author Commented:
btw, i increased points & i love you guys!!!
0
 
__Holly__Author Commented:
sorry am i not allowed to update the 'inserted' table??

0
 
__Holly__Author Commented:
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
 
__Holly__Author Commented:
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
 
__Holly__Author Commented:
aka the www.nigelrivett.com server
0
 
__Holly__Author Commented:
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
 
__Holly__Author Commented:
thanks--

i wish that i could see your scripts again--

0
 
blockwoodCommented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 14
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now