Audit Tables And Triggers
Jul 23, 2005
Dear Group,
I would like to create an audit table that is created with a trigger that
reflects all the changes(insert, update and delete) that occur in table.
Say I have a table with
Subject_ID, visit_number, dob, weight, height, User_name, inputdate
The audit table would have .
Subject_ID, visit_number, dob, weight, height, User_name, inputdate,
edit_action, edit_reason.
Where the edit_action would be insert, update, delete; the edit_reason would
be the reason given for the edit.
Help with this would be great, since I am new to the world of triggers.
Thanks,
Jeff
View 1 Replies
ADVERTISEMENT
Jul 20, 2005
i'm in a bit of a bind at work. if anyone could help, i'd greatlyappreciate it.i have a web app connecting to a sql server using sql serverauthentication. let's say, for example, my login/password isdbUser/dbUser. the web app however, is using windows authentication.so if I am logged into the network as 'DOMAINEric', when I access myweb app, my web app knows that I am 'DOMAINEric'. but to the sqlserver db, I am user 'dbUser'.now, i for each table i have, i need to implement an audit table torecord all updates, inserts, deletes that occur against it. i wasgoing to do so with triggers. this is all fine for selects, inserts,and updates. for each table, i have an updatedby and an updatedate.for example, let's say i have a table:create table blah(id int,col1 varchar(10),updatedby varchar(30),updatedate datetime)and corresponding audit table:create audit_blah(id int,blah_id int,blah_col1 varchar(10),blah_updatedby varchar(1),blah_updatedate datetime)for update and insert triggers, i can know what to insert into theupdatedby column of audit_blah because it's in a corresponding row inblah. my web app knows what user is accessing the application, andcan insert that name into blah. blah's trigger will then insert thatname into audit_blah.however, in the case of a delete, i'm not passing in an 'updatedby',because i'm deleting. in this situation, how can the trigger knowwhat user is deleting? the db only knows that sql user 'dbUser' isdeleting, but doesn't know that 'dbUser' is deleting on behalf of'DOMAINEric'. is there any way for my app to inform the trigger toaccess my windows identity without having a corresponding row in thetable from which to pull that info?obviously, i could have each of my app's users log into SQL serverthrough Windows authentication; then i could just use SYSTEM_USER.but let's say, for performance's sake, it'd be better for me to useone sql server login. (i believe one user works better for connectionpooling purposes.) is there a way to get around this?(i'm hoping a built-in function exists that solves all my problems.)suggestions? resources?any help would be great appreciated.happy turkeys.Eric
View 2 Replies
View Related
Jul 20, 2005
I need audit triggers that change columns value in the same recordthat fire trigger.I need how to do..Thanks..
View 11 Replies
View Related
Sep 23, 2004
Im using triggers to track changes Insert/Update/Deletes on my DB tables and they work for when i am manually adding/editing and deleting a single records.
The problem arises in that I have an asset/inventory management app that dumps lots of details into my DB tables at once each time its run.
Not all of the tables are updated and data cannot be completely inserted.
This is the trigger i have been using - could someone tell me how to modify it to work.
/*
This trigger audit trails all changes made to a table.
It will place in the table Audit all inserted, deleted, changed columns in the table on which it is placed.
It will put out an error message if there is no primary key on the table
You will need to change @TableName to match the table to be audit trailed
*/
ALTER trigger tr_TableName
on dbo.TableName for insert, update, delete
as
declare @bit int ,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(128) ,
@TableName varchar(128) ,
@PKCols varchar(1000) ,
@sql varchar(2000),
@UpdateDate varchar(21) ,
@Action nvarchar(50) ,
@HostName nvarchar(50),
@PKFieldName varchar (1000)
IF EXISTS(SELECT * FROM inserted)
IF EXISTS(SELECT * FROM deleted)
--update = inserted and deleted tables both contain data
BEGIN
SET @Action = 'UPDATE'
SELECT @DeviceID = (SELECT inserted.DeviceID FROM inserted INNER JOIN deleted ON inserted.deviceID = deleted.deviceid)
END
ELSE
--insert = inserted contains data, deleted does not
BEGIN
SET @Action = 'INSERT'
select @DeviceID = (SELECT DeviceID from inserted)
END
ELSE
--delete = deleted contains data, inserted does not
BEGIN
SET @Action = 'DELETE'
select @DeviceID = (SELECT DeviceID from deleted)
END
select @TableName = 'TableName'
-- date
select @HostName = host_name(),
@UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114),
--@DeviceID,
@PKFieldName=(select top 1 c.COLUMN_NAME fromINFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,INFORMATION_SCHEMA.KEY_COLUMN_USAGE c where pk.TABLE_NAME = @TableName
andCONSTRAINT_TYPE = 'PRIMARY KEY'andc.TABLE_NAME = pk.TABLE_NAME and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME)
-- get list of columns
select * into #ins from inserted
select * into #del from deleted
-- Get primary key columns for full outer join
select@PKCols = coalesce(@PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
fromINFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
andCONSTRAINT_TYPE = 'PRIMARY KEY'
andc.TABLE_NAME = pk.TABLE_NAME
andc.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
if @PKCols is null
begin
raiserror('no PK on table %s', 16, -1, @TableName)
return
end
select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
while @field < @maxfield
begin
select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field
select @bit = (@field - 1 )% 8 + 1
select @bit = power(2,@bit - 1)
select @char = ((@field - 1) / 8) + 1
--if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0
begin
select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
select @sql = 'insert LITE_Inventory (TableName, FieldName, OldValue, NewValue, UpdateDate, Action, Host, PkFieldName, DeviceID)'
-- select @sql = 'insert LITE_Inventory (TableName, FieldName, OldValue, NewValue, UpdateDate, Action, Host, PkFieldName)'
select @sql = @sql + ' select ''' + @TableName + ''''
select @sql = @sql + ',''' + @fieldname + ''''
select @sql = @sql + ',convert(varchar(1000),d.' + @fieldname + ')'
select @sql = @sql + ',convert(varchar(1000),i.' + @fieldname + ')'
select @sql = @sql + ',''' + @UpdateDate + ''''
select @sql = @sql + ',''' + @Action + ''''
select @sql = @sql + ',''' + @HostName + ''''
select @sql = @sql + ',''' + @PKFieldName + ''''
select @sql = @sql + ' from #ins i full outer join #del d'
select @sql = @sql + @PKCols
select @sql = @sql + ' where i.' + @fieldname + ' <> d.' + @fieldname
select @sql = @sql + ' or (i.' + @fieldname + ' is null and d.' + @fieldname + ' is not null)'
select @sql = @sql + ' or (i.' + @fieldname + ' is not null and d.' + @fieldname + ' is null)'
exec (@sql)
end
end
View 3 Replies
View Related
Jul 23, 2005
Hello.I tried to implement audit trail, by making an audit trail table with thefollowing fileds:TableName,FieldName,OldValue,NewValue,UpdateDate,t ype,UserName.Triggers on each table were set to do the job and everything was fine exceptthat in the audit trail you couldn't know which row exacltly wasupdated/inserted/deleted...Therefore I introduced 3 additional columnes(RowMark1, RowMark2, RowMark3) which should identify theinserted/updated/deleted row.For example, RowMark1 could be foreign key, RowMark2 could be primary key,and RowMark3 could be autonumber ID.But, when I have several rows updated, RowMark columnes values are identicalin all rows in the audit trail table! What is wrong with my code, and how tosolve it ?Thank you in advance!CREATE TRIGGER Trigger_audit_TableNameON dbo.TableNameFOR DELETE, INSERT, UPDATEAS BEGINdeclare @type nvarchar(20) ,@UpdateDate datetime ,@UserName nvarchar(100),@RowMark1 nvarchar (100),@RowMark2 nvarchar (100),@RowMark3 nvarchar (100)if exists (select * from inserted) and exists (select * fromdeleted)select @type = 'UPDATE',@RowMark1=d.ForeignKeyField,@RowMark2=d.PrimaryKey Field,@RowMark3=d.IDfrom deleted delse if exists (select * from inserted)select @type = 'INSERT',@RowMark1=i.ForeignKeyField,@RowMark2=i.PrimaryKey Field,@RowMark3=i.IDfrom inserted ielseselect @type = 'DELETE',@RowMark1=d.ForeignKeyField,@RowMark2=d.PrimaryKey Field,@RowMark3=d.IDfrom deleted dselect @UpdateDate = getdate() ,@UserName = USER/*The following code is repeated for every field in a table*/if update (FieldName) or @type = 'DELETE'insert dbo.AUDIT_TRAIL (TableName, FieldName, OldValue, NewValue,UpdateDate, UserName, type,RowMark1,RowMark2,RowMark3)select 'Descriptive Table Name', convert(nvarchar(100), 'DescriptiveField Name'),convert(nvarchar(1000),d.FieldName),convert(nvarchar(1000),i.FieldName),@UpdateDate, @UserName, @type, @RowMark1, @RowMark2,@RowMark3from inserted ifull outer join deleted don i.ID = d.IDwhere (i.FieldName <> d.FieldNameor (i.FieldName is null and d.FieldName is not null)or (i.FieldName is not null and d.FieldName is null))END
View 3 Replies
View Related
Jul 20, 2005
HiI am looking to implement an audit/history table/tables but am lookingat doing this without the use of triggers.The reason for doing this is that the application is highlytransactional and speed in critical areas is important.I am worried that triggers would slow things down.I am more used to other database where by there is a utility to "dump"the contents of the transaction logs and use this for auditingpurposes. However SQL Server does not have this functionality (unlessthere is a sql server tool - 3rd party that I do not know about)Has anyone implemented something similar? Or used/using a 3rd partytool that will do this job.Effectively the clients would like to "look" at what happened - say 15minutes ago.thanksjohn
View 3 Replies
View Related
Jan 11, 2008
I have ddl triggers in place to watch what people do to our various database environments. I can see when someone does something to a login, but I can't tell what was done. I have a sneaky someone creating accounts with sysadmin privs and I want to catch the source. I also want to know when someone changes a password on a sql account. Does anyone know of a way to do this?
___________________________
Geek At Large
View 6 Replies
View Related
Nov 26, 2007
Hi,
I am trying to figure out which of these option is best suited for auditing. Although each one of them has its own pros/cons.
CLR trigger is easy to write and can be made generic so that it fits for any table required to be audited.
I tried both the option in test database and i found the CLR trigger performed poorly.
Results were :
For table A (3 columns) with TSQL trigger took less than a sec for 2500 sequential inserts.
While table B (3 columns) having same structure with CLR trigger took more than 20 sec for 2500 sequential inserts.
Has anybody done performance comparision of this 2 approaches ?
Please share results if any.
I wanted to validate that is my findings correct so that i select best optimized approach.
Thanks,
Jignesh
View 1 Replies
View Related
Feb 6, 2006
Hi,
I am looking to track any changes made to any table within a db into a single audit table which will hold as fields: the table that has been updated/inserted, the field that was changed, its primary key, the old value and the new value specific to that field, and the date it was updated/inserted.
From what I have read, it does not look like this is possible with a trigger on table as it is not row specific and that I might have to control this from the business layer (vb.net). I am correct in this assumption, or is there a way of tracking specific data changes through triggers.
thanks
Welly
View 5 Replies
View Related
Jul 13, 2007
Hello. Taking a typical use having a history table, maintained from a base table via triggers... Trying to see how/if that can be done using the SQl 2005 Service Broker method, with messaging? The thought is that if we can do the History table updates ASYNC, the user will not wait more than setting up the Broker message queue. I saw this article about something similar, but it deals with LOGON triggers.
http://www.sqlservercentral.com/columnists/FVandeputte/sqlserver2005logontriggers.asp
I'd think you can't do Hisotyr type triggers, with a message, because wouldn't you need to write all teh INSERTED/UPDATED data somewhere anyways? and there could be multiple rows affected in any given insert/update/delete, so could you even pass that thru to a Broker?
Anyone know of any references to using Broker Services for sending INSERTED/UPDATED data along for Historical versioning?
Also, was curious about error handling, because say you update teh base table, and then a problem occurs, and the Hisotry table is not updated. I want them in sync. Where is the message data stored, and is it accesible even if teh server reboots before the data is RECEIVED from teh QUEUE?
Thanks, Bruce
View 7 Replies
View Related
Jun 8, 2015
I have a windows 2012 server and will like to know how to audit DMLs on a table (delete, truncate, update) on this table, I want to see all T-Sql DML statement carried out on this table in a file.How can this be achieved using if possible something already built into SSMS.
View 4 Replies
View Related
Jul 21, 2014
I would like to do a dynamic audit for the tables ( columns which needs to audited will also change dynamically). For example,
I am having 3 tables, table1 table2 table3
User can select Table 1 ( col 1, col3, col4) and Table2 ( col 3 and col 5).
I want to achieve this functionality without any triggers or output clause, so only one option which is left is "ChangeDataCapture" ( to my knowledge). So i did a small POC. The problem here is, system captures all the field data even though value doesn't change, for example,
TableA
CountryId CountryCode CountryName
1 IND INDIA
Update TableA
Set CountryCode = 'INDI', CountryName = 'INDIA'
Where CountryId = 1
On running the above query, CDC stores the value for both the columns, but i don't want this. I only wanted to store the CountryCode value alone.
CREATE TABLE [dbo].tCountry(
CountryId bigint IDENTITY(1,1) NOT NULL Primary Key,
CountryName [varchar](50) NULL,
CountryCode [varchar](50) NULL,
CreateBy bigint
)
EXEC sys.sp_cdc_enable_db
[code]....
--See here, i want to display CountryName as NULL, because i didnt do any changes in this column
--Why am i asking this bcoz, Frm my front end application, i will send the complete list of columns and the values to the update query.
Select * from cdc.dbo_tCountry_CT Where __$operation in (3,4)
View 3 Replies
View Related
Aug 31, 2007
I am trying to write triggers on each tables in my database to audit data changes. My AuditLog table consists of the following columns -
LoginName varchar(100) - user name
Action varchar(5) - this will store 'INSERT','UPDATE','DELETE'
TableName varchar(30) - name of the table updated
PrimaryKey int - primary key of the record updated
ColumnName varchar(30) - name of the column updated
OldValue varchar(1000) - old value converted to varchar
NewValue varchar(1000) - new value converted to varchar
RecUpdDate datetime - record update date.
This table design will work for tables with single column primary keys. However, it will not work for tables with composite primary keys. Any suggestions on how to make this work with composite primary keys? I prefer not to change the tables in my database to use single column primary key.
Thanks in advance.
View 3 Replies
View Related
Nov 1, 2014
I have made a server security audit and specify from database audit specification to audit "select" on a certain user and on a certain table. I logged in by this user and made the select statement..when i run this query
"select * from sys.fn_get_audit_file('d:Auditaudit1*',null,null)"
It return a value at which time the query has done
after 15 minutes i repeated the same action, i run the audit query and the same result is showed off on the panel.is it suppose to return a list of values by how many times this user has made the select statement on that table ? for example at 5:00 pm then 6:00 pm and so on
View 1 Replies
View Related
Jan 18, 2006
I need help...here is the problem.Last weekend, the servers in our datacenter where moved around. After thismove, and maybe coincidental, 1 server is performing very poor. Afterrunning a trace with SQL Profiler, I saw the problem which was laterconfirmed with another tool for SQL server performance monitoring. It seemsthat all connections to the SQL server (between 200 - 400) are doing a login/ logout for each command that they process. For example, the user'sconnection will login, perform a SELECT, and then logout. This is not a..NET application. The client software was not changed, it is still thesame. The vendor has said that it is not supposed to do that, it issupposed to use 1 connection that log's on in the morning and logs off atthe end of the day or whenever the user exits. 1 user may have severalconnections to the database.At times, the server is processing over 250 login / logouts (avgeraged for30 second period). Has anyone seen this problem? I have the server inAUDIT FAILUREs only. The server has become very unresponsive, things thattook 3 seconds now take over 15 seconds.Any ideas???
View 6 Replies
View Related
May 18, 2003
Hello, everyone. I am sorry I haven't been around for long but I have got this confusion. I haven't been able to set triggers between 2 tables.
I have got a set of data in 1 table and another set in the other table. I want to set the trigger by comparing the data column from one table to another.
All I want to know is is ti possible to do it? Because I have been told that I can only set triggers on only one table.
Any help is much appreciated.
Thanks in advance.
View 1 Replies
View Related
Apr 20, 1999
I'm thinking of building a trigger against a system table(sysobjects) in
database(a) on server(a) that will assist me in updating a table in
database(b)on server(b). What I need to know from table(b)is if a new table
has been added and removed from database(a) on server(a). I want to use a
store procedure to query the table(b). I was thinking of building a trigger
against my sysobjects table that would update table(b) whenever any tables
are added or removed from database(a). Has anyone built triggers against
system tables? I am running SQL 7.
View 2 Replies
View Related
Feb 14, 2008
Hi,
I have decided to use CLR Triggers to synchronize data between two different tables but I have a problem. The scenario is this. I do an insert in Table1. Table1 firest the trigger which then inserts a record in Table2. The trigger in Table2 fires a trigger which does an insert to Table1 again.
I need to find a way to disable the trigger so that it will not go in an infinite loop when I insert a record in either of the tables. What I have done so far is to disable Table2's trigger before I commit my insert to Table2 so that Table2's trigger won't fie. I will then enable the trigger after the insert is committed. Also, I have a webservice which manages the inserts/updates/deletes. I call webmethods from this webservice in my clr triggers.
Help please!
Thanks,
Ryan
View 5 Replies
View Related
Apr 15, 2008
How can we write triggers on system table dbo.sysobjects,
when i tries to write a trigger, it is giving an error that permission is denied.
I even give a permission of "allow modifications to system catalogs" in Enterprise manager.
still it is not giving permission.
How can i create a trigger on dbo.sysobjects table?
View 9 Replies
View Related
Feb 27, 2002
Does anyone know if it's possible to create a trigger on the sysdatabases table in the master database? I keep getting permission denied which I'm not sure is right.
View 2 Replies
View Related
Mar 1, 2002
Hi,
Does anyone know of a way I can create a trigger on a system table (say sysdatabases in master). I know this is not supported but presumably there's a way it can be done by referencing it's equivilant in Information_schema somewhere.
I'm trying to write a script that will automatically set up a backup schedule for a database that has just been created. I was hoping the trigger would query the sysdatabases table for new database name entries, log necessary info in an audit table and then call a backup script to set up the schedule. Any ideas??
Thanks
Rob
View 1 Replies
View Related
Mar 22, 2000
I am reading the WROX "Professional SQL Server 7 Programming" book.
The following code appears on page 424:
CREATE TRIGGER ProductIsRationed
ON Products
FOR UPDATE
AS
IF EXISTS
(
SELECT 'True'
FROM Inserted i
JOIN Deleted d
ON i.ProductID = d.ProductID
WHERE (d.UnitsInStock - i.UnitsInStock) > d.UnitsInStock / 2
AND d.UnitsInStock - i.UnitsInStock > 0
)
BEGIN
RAISERROR('Cannot reduce stock by more than 50%% at once.',16,1)
ROLLBACK TRAN
END
The trigger fires when an UPDATE is made to Products table. The author states that the Inserted and Deleted tables only exist for the life of the trigger, not before, and not after the trigger runs. If this is true, then why would there be any rows in the Deleted table in this case? No rows were
deleted within the trigger. As far as I can see, no rows have been updated
either. If the condition does exist, no rows will be updated, and an error will be displayed. Otherwise, the row will be updated. Then there would be a row in the inserted table. But then the trigger is finished and the inserted table for that trigger disappears. I think my logic is flawed, which is why I am writing. I don't think I fully understand the Inserted and Deleted tables.
Any help would be greatly appreciated.
Nathan
View 7 Replies
View Related
Jul 8, 2003
how do i return only the tables created by the user?
in three of my databases i am inserting one record per 5 secs. in all the tables. how good is using triggers for 'insert instead of' for these tables?
View 5 Replies
View Related
May 4, 2004
I have a table "PageInfo" wich has columns "PageID, Title(has "AboutUs" as one of the values), DateModified, Active". My other table is "AboutUs" and has columns "ID, WhatsNew, Welcome, Active".
My goal here is that if table "AboutUs" is affected as an "INSERT" or an "UPDATE" on any row, I want "PageInfo.DateModified" WHERE "PageInfo.Title = AboutUs" to be updated with "getDate()". Don't know if I'm clear enough but thanks in advance.
Gazzou
View 7 Replies
View Related
Apr 24, 2008
Hello,
I am trying to create a stored procedure that will disable triggers on any given table. Basically I want to pass in a table name as a parameter and query the master table for all triggers that belong to that table...then disable them. I just don't know how to get a list of triggers that belong to a table?
Thanks,
Andrew
View 2 Replies
View Related
Jul 20, 2005
When you import data using DTS into a table that has triggers - do the triggers fire off if there are triggers for on insert or on after insert?Thanks,--Micah
View 1 Replies
View Related
Dec 18, 2007
I'm know the above tables are system generated, am I right in assuming the following.
1) The tables are unique to the current user.
2) The tables only last as long as the transaction that caused their creation
Thanks in advance
Alex
View 1 Replies
View Related
Apr 29, 2004
I have a situation that I must resolve. I have a program being used by many but I had to create a new table to provide a new feature. The problem I have is this table must use the primary key from the parent table as its primary key, meaning when a user adds a new record to parent table, I need to instantly add the primary key to the child table. Now this was done in the program using sql statements, but I need to implement a trigger or such as to keep me from having to reinstall application on many computers.
basically person inserts new record, then I need to get the new primary ket and add insert it into the child tables. how can I do this with a trigger. I have tried to use an insert into statment with my trigger, but I can't seem to pass the parameters correctly.
CREATE Trigger dbo.Table_Borrower_Insert_Keys
ON Table_Borrower
AFTER INSERT
AS
begin
declare @bid as int
@bid = select MAX(BorrowerID)
FROM Table_SoldProgression
INSERT Table_SoldProgression(BorrowerID)
values (@bid)
end
GO
another attempt
CREATE Trigger dbo.Table_Borrower_Insert_Keys
ON Table_Borrower
AFTER INSERT
AS
INSERT Table_SoldProgression(BorrowerID)
values (select MAX(BorrowerID)FROM Table_Borrower)
GO
View 3 Replies
View Related
Sep 11, 2001
I want to compare the before and after values of an UPDATEd column using a trigger. I want to know if the value in the column has changed. Simple? No!
As you know, SqlServer puts the before image of the UPDATEd rows into the DELETED virtual table and the after image of the UPDATEd rows in the INSERTED virtual table.
So you would get the before and after data by doing a SELECT on these tables.
But here is the problem - how do you join the tables? What if there are >1 rows in these 2 tables (because the UPDATE affected >1 rows) - how do i know which "old"/DELETED rows correspond to which "new"/INSERTED?"
Ok - I could join the 2 tables on the primary key, but what if the primary key was updated? In that case the join would not work - the DELETED table would contain the old primary key value and the INSERTED table would contain the new (different) primary key value. In fact, ALL of the columns may have been changed by the UPDATE.
Now, there is another thing to try with triggers - the
IF UPDATE ( <columname> )
test. This is designed to tell you if a specified column was UPDATEd by the last UPDATE. However, this will return TRUE for any UPDATE that mentions the column - even if the UPDATE does not change any data! So I cannot determine whether a certain column has had its value changed with this either.
So then you can try another test mentioned in the docs for CREATE TRIGGER - the
IF COLUMNS_UPDATED()
test. However, this will report that a column has been updated, NOT whether the data has changed as aresult of that UPDATE.
So if you UPDATE the value in the column to the same value as it was beforehand (admittedly, a pointless thing to do, but it could happen in some apps), this fuction will say, yes, this column was updated.
So my question remains - how do I know if the data has changed in a column after an UPDATE, using a trigger?
Any ideas?
View 1 Replies
View Related
May 30, 2007
For my company, we have made it a standard to create history tables and triggers for the majority of our production tables. I recently grew tired of consistently spending the time needed to create these tables and triggers so I invested some time in creating a script that would auto generate these.
We recently launched a project which required nearly 100 history tables & triggers to be created. This would have normally taken a good day or two to complete. However, with this script it took a near 10 seconds. Here are some details about the script.
The code below creates a stored procedure that receives two input parameters (@TableName & @CreateTrigger) and performs the following actions:
1) Queries system tables to retrieve table schema for @TableName parameter
2) Creates a History table ("History_" + @TableName) to mimic the original table, plus includes additional history columns.
3) If @CreateTrigger = 'Y' then it creates an Update/Delete trigger on the @TableName table, which is used to populate the History table.
/************************************************************************************************************
Created By: Bryan Massey
Created On: 3/11/2007
Comments: Stored proc performs the following actions:
1) Queries system tables to retrieve table schema for @TableName parameter
2) Creates a History table ("History_" + @TableName) to mimic the original table, plus include
additional history columns.
3) If @CreateTrigger = 'Y' then it creates an Update/Delete trigger on the @TableName table,
which is used to populate the History table.
******************************************* MODIFICATIONS **************************************************
MM/DD/YYYY - Modified By - Description of Changes
************************************************************************************************************/
CREATE PROCEDURE DBO.History_Bat_AutoGenerateHistoryTableAndTrigger
@TableName VARCHAR(200),
@CreateTrigger CHAR(1) = 'Y' -- optional parameter; defaults to "Y"
AS
DECLARE @SQLTable VARCHAR(8000), @SQLTrigger VARCHAR(8000), @FieldList VARCHAR(6000), @FirstField VARCHAR(200)
DECLARE @TAB CHAR(1), @CRLF CHAR(1), @SQL VARCHAR(1000), @Date VARCHAR(12)
SET @TAB = CHAR(9)
SET @CRLF = CHAR(13) + CHAR(10)
SET @Date = CONVERT(VARCHAR(12), GETDATE(), 101)
SET @FieldList = ''
SET @SQLTable = ''
DECLARE @TableDescr VARCHAR(500), @FieldName VARCHAR(100), @DataType VARCHAR(50)
DECLARE @FieldLength VARCHAR(10), @Precision VARCHAR(10), @Scale VARCHAR(10), @FieldDescr VARCHAR(500), @AllowNulls VARCHAR(1)
DECLARE CurHistoryTable CURSOR FOR
-- query system tables to get table schema
SELECT CONVERT(VARCHAR(500), SP2.value) AS TableDescription,
CONVERT(VARCHAR(100), SC.Name) AS FieldName, CONVERT(VARCHAR(50), ST.Name) AS DataType,
CONVERT(VARCHAR(10),SC.length) AS FieldLength, CONVERT(VARCHAR(10), SC.XPrec) AS FieldPrecision,
CONVERT(VARCHAR(10), SC.XScale) AS FieldScale,
CASE SC.IsNullable WHEN 1 THEN 'Y' ELSE 'N' END AS AllowNulls
FROM SysObjects SO
INNER JOIN SysColumns SC ON SO.ID = SC.ID
INNER JOIN SysTypes ST ON SC.xtype = ST.xtype
LEFT OUTER JOIN SysProperties SP ON SC.ID = SP.ID AND SC.ColID = SP.SmallID
LEFT OUTER JOIN SysProperties SP2 ON SC.ID = SP2.ID AND SP2.SmallID = 0
WHERE SO.xtype = 'u' AND SO.Name = @TableName
ORDER BY SO.[name], SC.ColOrder
OPEN CurHistoryTable
FETCH NEXT FROM CurHistoryTable INTO @TableDescr, @FieldName, @DataType,
@FieldLength, @Precision, @Scale, @AllowNulls
WHILE @@FETCH_STATUS = 0
BEGIN
-- create list of table columns
IF LEN(@FieldList) = 0
BEGIN
SET @FieldList = @FieldName
SET @FirstField = @FieldName
END
ELSE
BEGIN
SET @FieldList = @FieldList + ', ' + @FieldName
END
IF LEN(@SQLTable) = 0
BEGIN
SET @SQLTable = 'CREATE TABLE [DBO].[History_' + @TableName + '] (' + @CRLF
SET @SQLTable = @SQLTable + @TAB + '[History' + @FieldName + '] [INT] IDENTITY(1,1) NOT NULL,' + @CRLF
END
SET @SQLTable = @SQLTable + @TAB + '[' + @FieldName + '] ' + '[' + @DataType + ']'
IF UPPER(@DataType) IN ('CHAR', 'VARCHAR', 'NCHAR', 'NVARCHAR', 'BINARY')
BEGIN
SET @SQLTable = @SQLTable + '(' + @FieldLength + ')'
END
ELSE IF UPPER(@DataType) IN ('DECIMAL', 'NUMERIC')
BEGIN
SET @SQLTable = @SQLTable + '(' + @Precision + ', ' + @Scale + ')'
END
IF @AllowNulls = 'Y'
BEGIN
SET @SQLTable = @SQLTable + ' NULL'
END
ELSE
BEGIN
SET @SQLTable = @SQLTable + ' NOT NULL'
END
SET @SQLTable = @SQLTable + ',' + @CRLF
FETCH NEXT FROM CurHistoryTable INTO @TableDescr, @FieldName, @DataType,
@FieldLength, @Precision, @Scale, @AllowNulls
END
CLOSE CurHistoryTable
DEALLOCATE CurHistoryTable
-- finish history table script with standard history columns
SET @SQLTable = @SQLTable + @TAB + '[HistoryCreatedOn] [DATETIME] NULL,' + @CRLF
SET @SQLTable = @SQLTable + @TAB + '[HistoryCreatedByUserID] [SMALLINT] NULL,' + @CRLF
SET @SQLTable = @SQLTable + @TAB + '[HistoryCreatedByUserName] [VARCHAR](30) NULL,' + @CRLF
SET @SQLTable = @SQLTable + @TAB + '[HistoryAction] [CHAR](1) NOT NULL' + @CRLF
SET @SQLTable = @SQLTable + ' )'
PRINT @SQLTable
-- execute sql script to create history table
EXEC(@SQLTable)
IF @@ERROR <> 0
BEGIN
PRINT '******************** ERROR CREATING HISTORY TABLE FOR TABLE: ' + @TableName + ' **************************************'
RETURN -1
END
IF @CreateTrigger = 'Y'
BEGIN
-- create history trigger
SET @SQLTrigger = '/************************************************************************************************************' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'Created By: ' + SUSER_SNAME() + @CRLF
SET @SQLTrigger = @SQLTrigger + 'Created On: ' + @Date + @CRLF
SET @SQLTrigger = @SQLTrigger + 'Comments: Auto generated trigger' + @CRLF
SET @SQLTrigger = @SQLTrigger + '***********************************************************************************************/' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'CREATE TRIGGER [Trigger_' + @TableName + '_UpdateDelete] ON DBO.' + @TableName + @CRLF
SET @SQLTrigger = @SQLTrigger + 'FOR UPDATE, DELETE' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'AS' + @CRLF + @CRLF
SET @SQLTrigger = @SQLTrigger + 'DECLARE @Action CHAR(1)' + @CRLF + @CRLF
SET @SQLTrigger = @SQLTrigger + 'IF EXISTS (SELECT ' + @FirstField + ' FROM Inserted)' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'BEGIN' + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + 'SET @Action = ''U''' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'END' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'ELSE' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'BEGIN' + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + 'SET @Action = ''D''' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'END' + @CRLF + @CRLF
SET @SQLTrigger = @SQLTrigger + 'INSERT INTO History_' + @TableName + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + '(' + @FieldList + ', HistoryCreatedOn, HistoryCreatedByUserName, HistoryAction)' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'SELECT ' + @FieldList + ', GETDATE(), SUSER_SNAME(), @Action' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'FROM DELETED'
--PRINT @SQLTrigger
-- execute sql script to create update/delete trigger
EXEC(@SQLTrigger)
IF @@ERROR <> 0
BEGIN
PRINT '******************** ERROR CREATING HISTORY TRIGGER FOR TABLE: ' + @TableName + ' **************************************'
RETURN -1
END
END
View 13 Replies
View Related
Jul 23, 2005
We have a partitioned view with 4 underlying tables. The view and eachof the underlying tables are in seperate databases on the same server.Inserts and deletes on the view work fine. We then add insert anddelete triggers to each of the underlying tables. The triggers modifya different set of tables in the same database as the view (differentthan the underlying table). The problem is those triggers aren't firedwhen inserting or deleteing via the view. Inserting or deleteing theunderlying table directly causes the the triggers to fire, but not whenthe tables are accessed as a result of using the view.Am I missing something? The triggers are 'for insert' and 'fordelete'. No 'instead of' or 'after' triggers.
View 4 Replies
View Related
Mar 6, 2002
I want to be able to duplicate every single record that is inserted or updated in a particular table to another table, but not the delete. Is the best way to set-up a trigger? If so can anyone provide me with an example of how to do this? Also could you just duplicate certain columns in the row I would you have to do all columns?
Thanks for help.
View 2 Replies
View Related
Sep 11, 2014
I have many new tables for which i need to write Insert,Update and delete triggers manually. Is there any way to generate triggers script which takes table name as a input parameter and print/generate trigger's script?
View 1 Replies
View Related
