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
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.
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?
We have a need to report on historical data when none exists in the database.
Create a tabular report rdl that is run on a regular schedule. This report run is saved as an Excel sheet that overwrites the previous run, which has the same name.
Report is designed to use two data sources--the database and and the previously run Excel sheet. The data is "merged" using the Lookup function thereby creating a new report that includes the history needed.
Can anyone help, I am able to create a trigger that will populate a audit table everytime one of my tables columns data changes, but I have an applications from another user that has a stored proceudre and when that is called from an application it hit the original table twice, so the audit table will get a duplicate entry. How do you prevent an AUDIT TABLE from inserting a duplicate entry Here is my trigger:Create TRIGGER tg_audit_task_order_awardees on xxxfor INSERT,UPDATE,DELETE as INSERT INTO audit_task_order_awardees( audit_log_type, to_awardee, solicitation_id, contract_id, order_number, amount, show_public, audit_changedatetime, audit_user) Select 'OLD', del.to_awardee, del.solicitation_id, del.contract_id, del.order_number, del.amount, del.show_public, getdate(), del.modified_user FROM deleted del
/* for a new record */ INSERT INTO audit_task_order_awardees( audit_log_type, to_awardee, solicitation_id, contract_id, order_number, amount, show_public, audit_changedatetime, audit_user) Select 'NEW', ins.to_awardee, ins.solicitation_id, ins.contract_id, ins.order_number, ins.amount, ins.show_public, getdate(), ins.modified_user FROM inserted ins
I need to create a simple audit trigger for a table with 12 columns. I need to determine which row was changed. Is there a simple way to do that. The table structure is ID Integer(4) barcode(25) epc_tag(24) bc_stop_flag(1) reject_flag(1) complete_flag(1) hold_flag(1) pe_1_flag pe_2_flag pe_3_flag pe_4_flag pe_5_flag
Trying to create an audit that goes to the application log. With a certain criteria, select, update, delete etc.. where like 'example'-- some criteria..is there any reference out there that could give me some examples?
I have to create a history of tables. Therefore I must know the previous structure of the table. I have to document the changes of the structures of the tables. Is there a solution for the version 2000?
Hi all, please help. I m trying to create an "empty" table from existing table for the audit trigger purpose. For now, i am trying to create an empty audit table for every table in a database named "pubs", and it's seem won't work. Please advise.. Thanks in advance.
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE= 'BASE TABLE' AND TABLE_NAME NOT LIKE 'audit%' AND TABLE_NAME!= 'sysdiagrams' AND TABLE_NAME!= 'Audit' AND TABLE_NAME = 'sales'
WHILE @TABLE_NAME IS NOT NULL BEGIN
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME> @TABLE_NAME AND TABLE_NAME = 'sales'
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME> @TABLE_NAME AND TABLE_TYPE= 'BASE TABLE' AND TABLE_NAME!= 'sysdiagrams' AND TABLE_NAME!= 'Audit' AND TABLE_NAME NOT LIKE 'audit%'
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
-- 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
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
How can we programmatically cause a Manual Report History Snapshot to be created.
I know it can be done using the Webservice. How would we do it using the ReportServer stored procedures. Which SP/SP's can we call. With what parameters.
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
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???
Hi all, I would just like to ask whats the best way to make some audit on some of the tables in a MS SQL server, what i'm planning to have is to have a table which can contain all changes/inserts/deletion of some given tables, my first idea was to have this:
AuditTable that have the following fields: AuditID, TableName, FieldName, OldValue, NewValue, UpdateBy, UpdateDate
then in all the given tables, i'll have insert, update and delete trigger, the issue comes down to the trigger, what will be the best way to have that trigger written in a way that it can be use for other tables as well? say if a table have more then 20 fields, I don't want to declare 20 var and compare them 1 by1, and if there is a diff, then i insert to the audittable, I want something that it can loop and (if possible) be able to use by other table as well, so the field name etc can get from sysobjects, but then how can you code it in a way that it can do that?
Or is there any better way to get the same result? currently i have an audit table for each table i want to audit on and its just wasting space, any help will be great. Thanks,
I am setting up SQL audit on sql servers in my environment based on requirement. I want to create database specifications ASAP database created. I tried DDL trigger but Audit doesn't support triggers. So I created audit specifications on model database. the only problem with this is every specification created on new database with same name.database specification name includes newly created database name or other methods to create database specifications on newly created databases.
Hi all, please help. I m trying to create an "empty" table from existing table for the audit trigger purpose. For now, i am trying to create an empty audit table for every table in a database named "pubs", and it's seem won't work. Please advise.. Thanks in advance.
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE= 'BASE TABLE' AND TABLE_NAME NOT LIKE 'audit%' AND TABLE_NAME!= 'sysdiagrams' AND TABLE_NAME!= 'Audit' AND TABLE_NAME = 'sales'
WHILE @TABLE_NAME IS NOT NULL BEGIN
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME> @TABLE_NAME AND TABLE_NAME = 'sales'
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME> @TABLE_NAME AND TABLE_TYPE= 'BASE TABLE' AND TABLE_NAME!= 'sysdiagrams' AND TABLE_NAME!= 'Audit' AND TABLE_NAME NOT LIKE 'audit%'
Is there any option present in SSMS that ll audit all the changes(insert/update/delete) done to data in a database from SSMS( not through any application/front end).
I dont want to place any trace or triggers, is there any in-built option.
Here is the situation:Tables:AccountAccountDocumentAccountTestThen we have the following "Activity" table refencing "Account" tableabove:Table: ActivityColumns: ActivityID, AccountNo,...when "Activity" with accountno is created, we'd like to able to takesnapshop or copy all Account related data records from tables: account,accountdocument, accounttestand store it somewhere.This way even if account, accountdocument and accounttest tables changelater, then "Activity" accountno integrity in maintained.Do we need to create or replicate similar tables to store data foraudit?or Which other ways can this be achieved?
Hi,I want my application to audit any data changes (update, insert,delete) made by the users. Rather than have an audit table mirroringeach user table, I'd prefer to have a generic structure which can loganything. This is what I've come up with:TABLE: audit_record*audit_record_id (uniqueidentifier, auto-assign, PK) - uniqueidenfiier of the audit recordtable_name (varchar) - name of the table where the action (insert/update/delete) was madepk_value (varchar) - primary key of the changed record. If the PKitself has changed, this will store the old value.user_id (varchar) - user who changed the recorddate (datetime) - date/time at which the change was madeaction (int) - 0, 1 or 2 (insert, update, delete)TABLE: audit_column*audit_record_id (uniqueidentifier, composite PK) - FK tocdb_audit_record table*column_name (varchar, composite PK) - name of the column with changeddatanew_value (text?) - value after the changeSo every column which changes has its new value logged individually inthe audit_column table. However, I'm not sure what data type thenew_value column should have. The obvious answer (to me) is text, asthat can handle any necessary data type with the appropriateconversion (we don't store any binary data). However, this table isgoing to grow to millions of records and I'm not sure what theperformance implications of a text column will be, particularly giventhat the actual data stored in it will almost always be tiny.Any thoughts/recommendations/criticism would be greatly appreciated.ThanksAlex
We are using a SQL Server 2000 Replication. I'm using the Merge Agent History Screen to retrive Informacion about replication sessions, is there any other screen to know exaclty which datawas replicated on each session?Or at least to know the script generated on each session?
I need to record in a table: Who, When, What Field and New Value of Fields When changes occur to an existing record.
The purpose is for users to occassionally view the changes. They'll want to be able to see the history of the record - who changed what and when.
I figured I'd add the needed code to the stored procedure that's doing the update for the record.
When the stored procedure is called to do the update, the PK and parameters are sent.
The SP could first retain the current state of the record from the disk, then do the update, then "spin" thru the fields comparing the record state prior to the update and after. Differences could be parsed to a "Changes string" and in the end, this string is saved in a history record along with a few other fields:
Name, DateTime, Changes
FK to Changed Record: some int value Name: Joe Blow Date: 1/1/05 12:02pm Changes: Severity: 23 Project: Everest Assigned Lab: 204
How does the above approach sound?
Is there a better way you'd suggest?
Any sample code for a system that spins thru the fields comparing 1 temporary record with another looking for changes?
I have to make a stored procedure that will show the history and changes made to a given EmpNo, with the UpdateDate, UpdateUser and indicate which field is modified. Ex. Employee Mobile number was changed from '134151235' to '23523657'.
Result must be:
EmpNo | UpdateDate | UpdateUser | Field changed | Change from | change to
writing a T_SQL query for the following scenario..I have a SQL DB Audit file that gets populated with data as the activity on DB goes on.I have multiple monthly tables setup that the import should go into these monthly tables based on the event_time value in the SQL DB Audit file.all the data Like event_time '2015-08-25 15:59:39.033' should go to SQL table Audit_tbl_Aug2015 Query for reading SQLDB Audit file
SELECT * FROM sys.fn_get_audit_file ('C:ackupAudit*',default,default) order by event_time desc GO --DML for Audit table CREATE TABLE [dbo].[Audit_tbl_Aug2015]( [id] [bigint] IDENTITY(1,1) NOT NULL, [event_time] [datetime2](7) NOT NULL, [sequence_number] [int] NULL, [action_id] [varchar](4) NULL, [succeeded] [bit] NOT NULL, [permission_bitmask] [bigint] NOT NULL, [is_column_permission] [bit] NOT NULL, [session_id] [smallint] NOT NULL, [server_principal_id] [int] NULL, [database_principal_id] [int]
1. Does SSRS is J-Sox Compliant, an application must have audit trail feature. For Reporting printing, it should facilitate reports' data logging process.
2. Information like WHO and WHEN printed the report and WHAT data was viewed?
When I execute the below stored procedure I get the error that "Arithmetic overflow error converting expression to data type int".
USE [FileSharing] GO /****** Object: StoredProcedure [dbo].[xlaAFSsp_reports] Script Date: 24.07.2015 17:04:10 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
[Code] .....
Msg 8115, Level 16, State 2, Procedure xlaAFSsp_reports, Line 25 Arithmetic overflow error converting expression to data type int. The statement has been terminated. (1 row(s) affected)
I have a master securities table which has 7 fields. As a part of the daily process I am uploading flat files into database tables. The flat files contains the master(static) security data as well as the analytics(transaction) data. I need to
1) separate the master (static) data from the flat files,
2) check whether that data is present in the master table, if not then insert that data into the master table
3) If data present then move that existing record to an history table and then update the main master table.
All the 7 fields need to be checked to uniquely identify a single record in the master table.
How can this be done? Whether we can us a combination of data flow items or write a sql procedure to do all this.
I am using a stored procedure which returns a value of charecter datatype 'V' to the calling program.I am getting an sql exception System.Data.SqlClient.SqlException: Syntax error converting the varchar value 'V' to a column of data type inti didnot define any int datatype in my tablethis is my codeSqlCommand com = new SqlCommand("StoredProcedure4", connection);com.CommandType = CommandType.StoredProcedure; SqlParameter p1 = com.Parameters.Add("@uname", SqlDbType.NVarChar);SqlParameter p2 = com.Parameters.Add("@opwd", SqlDbType.NVarChar);SqlParameter p3 = com.Parameters.Add("@role", SqlDbType.NVarChar);p3.Direction = ParameterDirection.ReturnValue;p1.Value = username.Text.Trim();p2.Value = password.Text.Trim();com.ExecuteReader();lblerror2.Text = (string)(com.Parameters["@role"].Value); can your figure out what is the error ? Is it a coding error or error of the databse
I have a text file which needs to be created into a table (let's call it DataFile table). For now I'm just doing the manual DTS to import the txt into SQL server to create the table, which works. But here's my problem....
I need to extract data from DataFile table, here's my query:
select * from dbo.DataFile where DF_SC_Case_Nbr not like '0000%';
Then I need to create a new table for the extracted data, let's call it ExtractedDataFile. But I don't know how to create a new table and insert the data I selected above into the new one.
Also, can the extraction and the creation of new table be done in just one stored procedure? or is there any other way of doing all this (including the importation of the text file)?
