Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Create Trigger Programmatically

Posted on 2002-07-06
20
Medium Priority
?
819 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__
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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 2000 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

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.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

661 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