Trigger Doesn't Log Into The Audit Table If The Column Of Table Has Trigger On Is Null
Jan 23, 2008
Hi,
I have a trigger set on TABLE1 so that any update to this column should set off trigger to write to the AUDIT log table, it works fine otherwise but not the very first time when table1 has null in the column. if i comment out
and i.req_fname <> d.req_fname from the where clause then it works fine the first time too. Seems like null value of the column is messing things up
Any thoughts?
Here is my t-sql
Insert into dbo.AUDIT (audit_req, audit_new_value, audit_field, audit_user)
select i.req_guid, i.req_fname, 'req_fname', IsNull(i.req_last_update_user,@default_user) as username from inserted i, deleted d
where i.req_guid = d.req_guid
and i.req_fname <> d.req_fname
Thanks,
leo
View 7 Replies
ADVERTISEMENT
Dec 5, 2006
This Audit Trigger is Generic (i.e. non-"Table Specific") attach it to any tabel and it should work. Be sure and create the 'Audit' table first though.
The following code write audit entries to a Table called
'Audit'
with columns
'ActionType' //varchar
'TableName' //varchar
'PK' //varchar
'FieldName' //varchar
'OldValue' //varchar
'NewValue' //varchar
'ChangeDateTime' //datetime
'ChangeBy' //varchar
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public partial class Triggers
{
//A Generic Trigger for Insert, Update and Delete Actions on any Table
[Microsoft.SqlServer.Server.SqlTrigger(Name = "AuditTrigger", Event = "FOR INSERT, UPDATE, DELETE")]
public static void AuditTrigger()
{
SqlTriggerContext tcontext = SqlContext.TriggerContext; //Trigger Context
string TName; //Where we store the Altered Table's Name
string User; //Where we will store the Database Username
DataRow iRow; //DataRow to hold the inserted values
DataRow dRow; //DataRow to how the deleted/overwritten values
DataRow aRow; //Audit DataRow to build our Audit entry with
string PKString; //Will temporarily store the Primary Key Column Names and Values here
using (SqlConnection conn = new SqlConnection("context connection=true"))//Our Connection
{
conn.Open();//Open the Connection
//Build the AuditAdapter and Mathcing Table
SqlDataAdapter AuditAdapter = new SqlDataAdapter("SELECT * FROM Audit WHERE 1=0", conn);
DataTable AuditTable = new DataTable();
AuditAdapter.FillSchema(AuditTable, SchemaType.Source);
SqlCommandBuilder AuditCommandBuilder = new SqlCommandBuilder(AuditAdapter);//Populates the Insert command for us
//Get the inserted values
SqlDataAdapter Loader = new SqlDataAdapter("SELECT * from INSERTED", conn);
DataTable inserted = new DataTable();
Loader.Fill(inserted);
//Get the deleted and/or overwritten values
Loader.SelectCommand.CommandText = "SELECT * from DELETED";
DataTable deleted = new DataTable();
Loader.Fill(deleted);
//Retrieve the Name of the Table that currently has a lock from the executing command(i.e. the one that caused this trigger to fire)
SqlCommand cmd = new SqlCommand("SELECT object_name(resource_associated_entity_id) FROM
ys.dm_tran_locks WHERE request_session_id = @@spid and resource_type = 'OBJECT'", conn);
TName = cmd.ExecuteScalar().ToString();
//Retrieve the UserName of the current Database User
SqlCommand curUserCommand = new SqlCommand("SELECT system_user", conn);
User = curUserCommand.ExecuteScalar().ToString();
//Adapted the following command from a T-SQL audit trigger by Nigel Rivett
//http://www.nigelrivett.net/AuditTrailTrigger.html
SqlDataAdapter PKTableAdapter = new SqlDataAdapter(@"SELECT c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = '" + TName + @"'
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME", conn);
DataTable PKTable = new DataTable();
PKTableAdapter.Fill(PKTable);
switch (tcontext.TriggerAction)//Switch on the Action occuring on the Table
{
case TriggerAction.Update:
iRow = inserted.Rows[0];//Get the inserted values in row form
dRow = deleted.Rows[0];//Get the overwritten values in row form
PKString = PKStringBuilder(PKTable, iRow);//the the Primary Keys and There values as a string
foreach (DataColumn column in inserted.Columns)//Walk through all possible Table Columns
{
if (!iRow[column.Ordinal].Equals(dRow[column.Ordinal]))//If value changed
{
//Build an Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "U";//U for Update
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = dRow[column.Ordinal].ToString();
aRow["NewValue"] = iRow[column.Ordinal].ToString();
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the entry
}
}
break;
case TriggerAction.Insert:
iRow = inserted.Rows[0];
PKString = PKStringBuilder(PKTable, iRow);
foreach (DataColumn column in inserted.Columns)
{
//Build an Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "I";//I for Insert
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = null;
aRow["NewValue"] = iRow[column.Ordinal].ToString();
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry
}
break;
case TriggerAction.Delete:
dRow = deleted.Rows[0];
PKString = PKStringBuilder(PKTable, dRow);
foreach (DataColumn column in inserted.Columns)
{
//Build and Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "D";//D for Delete
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = dRow[column.Ordinal].ToString();
aRow["NewValue"] = null;
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry
}
break;
default:
//Do Nothing
break;
}
AuditAdapter.Update(AuditTable);//Write all Audit Entries back to AuditTable
conn.Close(); //Close the Connection
}
}
//Helper function that takes a Table of the Primary Key Column Names and the modified rows Values
//and builds a string of the form "<PKColumn1Name=Value1>,PKColumn2Name=Value2>,......"
public static string PKStringBuilder(DataTable primaryKeysTable, DataRow valuesDataRow)
{
string temp = String.Empty;
foreach (DataRow kColumn in primaryKeysTable.Rows)//for all Primary Keys of the Table that is being changed
{
temp = String.Concat(temp, String.Concat("<", kColumn[0].ToString(), "=", valuesDataRow[kColumn[0].ToString)].ToString(), ">,"));
}
return temp;
}
}
The trick was getting the Table Name and the Primary Key Columns.
I hope this code is found useful.
Comments and Suggestion will be much appreciated.
View 16 Replies
View Related
Oct 22, 2012
I write a insert trigger on my table LeaveRegister(1000 rows) and inserting rows in audit table, but when i inserting a row in LeaveRegister table. In audit table 1000 + 1 rows are inserting every time.
View 6 Replies
View Related
Aug 30, 2013
The following is the trigger which create a row in the audit table when a single deletion is occurred.
ALTER TRIGGER [dbo].[TRG_Delete_tbl_attendance]
ON [dbo].[tbl_attendance]
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
[code]....
I am trying to create a trigger which should prevent the bulk deletion. The following is the trigger which I have written, it is preventing the bulk deletion. But the problem is, it is removing the single deletion entries in the audit table. I want audit table to hold back the single deletion entries without allowing the bult deletion
ALTER TRIGGER [dbo].[TRG_Delete_Bulk_tbl_attendance]
ON [dbo].[tbl_attendance]
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
[code]....
View 1 Replies
View Related
Mar 8, 2008
I have a Trigger Instead of Update.
When I Update my table, the trigger runs correctly but my table never is updated.
View 5 Replies
View Related
May 16, 2006
Hi ,
I have 2 tables (Dept and Emp)
The columns in table Dept are Deptno and Deptname. Deptno is bigint and it is primary key. In Emp table, columns are Empno(PK) ,EmpName and Deptno(foreign key referring to Dept)
To Insert or Update record in Emp through application, value of Deptno is coming as 0(Zero). I want the value of Deptno to be inserted or updated as null if the value is Zero (0). How to do this in sql server 2005 by using trigger on table Emp
Thanks in advance
regards,
Srinivas Govada
View 6 Replies
View Related
Jul 6, 2015
I need a trigger to know who and when a char(1) column is changed. Would like to write the audit trail to its own table I can query and record before and after values.
DDL:
CREATE TABLE [dbo].[Test](
[Customer] [varchar](12) NULL,
[Active] [char](1) NULL DEFAULT ('N') --Must use char 1 b/c more than 2 possible values
)
Insert into Test (Customer, Active) Values ('Acme','Y')..I want trigger to tell me whowhenwhere this value was changed. If using sql auth capture client windows id if possible and write to audit table Update Test set Active = 'N'
View 6 Replies
View Related
Nov 14, 2006
Hey,
I'm new to this whole SQL Server 2005 thing as well as database design and I've read up on various ways I can integrate business constraints into my database. I'm not sure which way applies to me, but I could use a helping hand in the right direction.
A quick explanation of the various tables I'm dealing with:
WBS - the Work Breakdown Structure, for example: A - Widget 1, AA - Widget 1 Subsystem 1, and etc.
Impacts - the Risk or Opportunity impacts for the weights of a part/assembly. (See Assemblies have Impacts below)
Allocations - the review of the product in question, say Widget 1, in terms of various weight totals, including all parts. Example - September allocation, Initial Demo allocation, etc. Mostly used for weight history and trending
Parts - There are hundreds of Parts which will eventually lead to thousands. Each part has a WBS element. [Seems redundant, but parts are managed in-house, and WBS elements are cross-company and issued by the Government]
Parts have Allocations - For weight history and trending (see Allocations). Example, Nut 17 can have a September 1st allocation, a September 5th allocation, etc.
Assemblies - Parts are assemblies by themselves and can belong to multiple assemblies. Now, there can be multiple parts on a product, say, an unmanned ground vehicle (UGV), and so those parts can belong to a higher "assembly" [For example, there can be 3 Nut 17's (lower assembly) on Widget 1 Subsystem 2 (higher assembly) and 4 more on Widget 1 Subsystem 5, etc.]. What I'm concerned about is ensuring that the weight roll-ups are accurate for all of the assemblies.
Assemblies have Impacts - There is a risk and opportunity impact setup modeled into this design to allow for a risk or opportunity to be marked on a per-assembly level. That's all this table represents.
A part is allocated a weight and then assigned to an assembly. The Assemblies table holds this hierarchical information - the lower assembly and the higher one, both of which are Parts entries in the [Parts have Allocations] table.
Therefore, to ensure proper weight roll ups in the [Parts have Allocations] table on a per part-basis, I would like to check for any inserts, updates, deletes on both the [Parts have Allocations] table as well as the [Assemblies] table and then re-calculate the weight roll up for every assembly. Now, I'm not sure if this is a huge performance hog, but I do need to keep all the information as up-to-date and as accurate as possible. As such, I'm not sure which method is even correct, although it seems an AFTER DML trigger is in order (from what I've gathered thus far). Keep in mind, this trigger needs to go through and check every WBS or Part and then go through and check all of it's associated assemblies and then ensure the weights are correct by re-summing the weights listed.
If you need the design or create script (table layout), please let me know.
Thanks.
View 4 Replies
View Related
Jul 20, 2005
Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'
View 1 Replies
View Related
Oct 30, 2007
Table 1
First_Name
Middle_Name
Surname
John
Ian
Lennon
Mike
Buffalo
Tyson
Tom
Finney
Jones
Table 2
ID
F
M
S
DOB
1
Athony
Harold
Wilson
24/4/67
2
Margaret
Betty
Thathcer
1/1/1808
3
John
Ian
Lennon
2/2/1979
4
Mike
Buffalo
Tyson
3/4/04
5
Tom
Finney
Jones
1/1/2000
I want to be able to create a trigger that updates table 2 when a row is inserted into table 1. However Im not sure how to increment the ID in table 2 or to update only the row that has been inserted.
View 17 Replies
View Related
Feb 5, 2008
A
ID
Name
1
Joe
2
Fred
3
Ian
4
Bill
B
ID
1
4
I want to be able to create a trigger so that when a row is inserted into table A by a specific user then the ID will appear in table B. Is it possible to find out the login id of the user inserting a row?
I believe the trigger should look something like this:
create trigger test_trigger
on a
for insert
as
insert into b(ID)
select i.id
from inserted i
where
--specific USER
View 9 Replies
View Related
Sep 25, 2014
I need to create trigger on table which will not allow to update value "1" into column and if tried to update.. then it should show error massage "Good To GO"
ID Name Roll
1 Ron 1
2 Jon 0
3 Nil 3
4 Par 1
if you try to update value "1" in Roll then it will through error
nil
View 4 Replies
View Related
May 5, 2008
Hi all,
I'm new to this whole trigger 'thing', so forgive me if this question has been asked and answered a few times already.
I'm in the process of writing a trigger that will send an e-mail to an application admin when any table within a given database is altered (IE, a column is added or deleted). I can get the e-mail to fire off when that happens without any issue, but I'd like to be able to let the admin know which table was tweaked and what new column was added.
Is this a relatively easy thing to do and I'm just not finding the right built-in variable name, or does something more need to be done?
Thanks,
-Mike
View 4 Replies
View Related
Nov 7, 2006
Hello,i have a table and if a record is inserted i will test a numeric valuein this table. If the this value is greather as 1 million, than anstatus column should be changed from 'A' to 'B'.Yes and sorry, this is a newbie question.On Oracle this work simple:create trigger myTrigger on tableXasbeginif :old.x 100000 then:new.y:='B'end if;end;ThanksMaik
View 2 Replies
View Related
Sep 26, 2007
Hello again,
I'm hoping someone can help with with a task I've been given. I need to write a trigger which will act effectively as a method of automatically distributing of incoming call ticket records. See DDL below for creation of the Assignment table, which holds information on the call ticket workload.
Code Snippet
CREATE TABLE #Assignment
(CallID INT IDENTITY(1500,1) PRIMARY KEY,
AssignmentGroup VARCHAR(25),
Assignee VARCHAR(25)
)
GO
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('Service Desk', 'Jim Smith')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('PC Support', 'Donald Duck')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('Service Desk', 'Joe Bloggs')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('Service Desk', 'Joe Bloggs')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('Service Desk', 'Joe Bloggs')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('PC Support', 'Donald Duck')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('PC Support', 'Mickey Mouse')
GO
SELECT COUNT(CallID) AS [Total Calls], AssignmentGroup, Assignee
FROM #Assignment
GROUP BY AssignmentGroup, Assignee
ORDER BY COUNT(CallID) DESC , AssignmentGroup, Assignee
What I need to do is write a trigger for on INSERT to automatically update the Assignee column with the name of the person who currently has the least active calls. For example, using the data above, the next PC Support call will go to Mickey Mouse, and the next two Service Desk calls will go to Jim Smith.
So, the logic for the trigger would be
UPDATE #Assignment
SET Assignee = (SELECT Assignee FROM #Assignment WHERE COUNT(CallID) = MIN(COUNT(CallID))
But that's only the logic, and obviously it doesn't work with the syntax being nothing like correct.
Does any one have an idea or pointers as to how I should go about this?
Grateful for any advice, thanks
matt
View 5 Replies
View Related
Feb 22, 2008
Hi
I have a situation below
Table : tblStatus
Column1 : Event_Desc
Column2 : Flag
Once the flag is set to "T", I want to trigger a SSIS package named "PerformAction.dtsx"
How to write a table trigger for this.
With Regards,
Madhu
View 7 Replies
View Related
Jun 5, 2007
hi, im kinda new to sql and was just wondering if someone could help.
i need to create a trigger that saves all changes from a table(table1) to an audit table.
-table1-
item_code
item_price
item_description
View 2 Replies
View Related
Oct 26, 2007
I wont to get text of statement which chaged actual table.
with this script :
Code Block
if object_id('dbo.mytable') is not null drop table dbo.mytable
go
create table dbo.mytable (f1 varchar(100))
go
alter trigger dbo.ti_mytable on dbo.mytable for insert
as
set nocount on
print 'in trigger'
DECLARE @sql_handle binary(20)
select @sql_handle = sql_handle FROM master.dbo.sysprocesses p (nolock) where spid = @@spid
select @sql_handle, @@spid, [text] FROM ::fn_get_sql(@sql_handle) as statement_text
GO
if I execute
Code Block
insert into mytable values ('test')
I getting text of the trigger. How I can get @sql_handle of original statement ?
View 1 Replies
View Related
May 19, 2014
I am trying to update one table when records are inserted in another table.
I have added the following trigger to the table “ProdTr” and every time a record is added I want to update the field “Qty3” in the table “ActInf” with a value from the inserted record.
My problem appears to be that I am unable to fill the variables with values, and I cannot understand why it isn’t working, my code is:
ALTER trigger [dbo].[antall_liter] on [dbo].[ProdTr]
for insert
as
begin
declare @liter as decimal(28,6)
[Code] ....
View 4 Replies
View Related
Nov 2, 2004
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
View 3 Replies
View Related
May 29, 2006
I have a trigger on my Table (MyTable). The Trigger saves any changes to mytable. The AuditTrails table has this columns: ID, TableName,PrimaryKey, OLDvalue, NewValue,UserID,DateCreated.
Now, how can i pass the application's UserID (Not the SQL Server User) and save it to the AuditTrails table?
In my .NET Application, if someone delete a record from myTable how can i possibly get the userid of that person using my Audit trail Trigger?
View 5 Replies
View Related
Jan 21, 2008
Hi all,
I'm trying to create a audit trail trigger. I'm new to SQL Server. For simplicity sake, let's just say I have a table like the following:
Table1
FieldA
FieldB
And the audit table is:
Table1_Audit
FieldA
FieldB
Operation (Insert/Update/Delete)
Operator (Username)
Op_Date (GetDate())
Ok, simple enough. Now, I'm using Studio Express and there are several templates available. By default this is the one I get:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER <Schema_Name, sysname, Schema_Name>.<Trigger_Name, sysname, Trigger_Name>
ON <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name>
AFTER <Data_Modification_Statements, , INSERT,DELETE,UPDATE>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
END
GO
Not sure if I need all of this information? Examples I've found on the web look very confusing...ugh. I looking for simple (if possible).
Many thanks in advance,
Mark
View 2 Replies
View Related
Oct 13, 2006
I want to insert a row for a Global user in Table 'A' whenever ADO.Net code inserts a Local user row into same table. I recommended using a trigger to implement this functionality, but the DBA was against it, saying that stored proecedures should be used, since triggers are unreliable and slow down the system by placing unecessary locks on the table. Is this true OR the DBA is saying something wrong? My thinking is that Microsoft will never include triggers if they are unreliable and the DBA is just wanting to offload the extra DBA task of triggers to the programmer so that a stored procedure is getting called, so he has less headache on his hands.Thanks
View 2 Replies
View Related
Jul 30, 2015
If the id1 will change in table1 it should also change the corresponding id1 field in table2 it does not do anything.
CREATE TRIGGER [dbo].[IDCHANGE]
ON [dbo].[table1]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
[Code] .....
View 1 Replies
View Related
Jul 11, 2006
I want to pass the 'inserted' table from a trigger into an SP, I think I need to do this by dumping inserted table into a temporary table and passing the temp table. However, I need to do this for many tables, and don't want to list all the column names for each table/trigger (maintenance nightmare).
Can I dump the 'inserted' table to a temp table WITHOUT specifying the column names?
View 16 Replies
View Related
Oct 14, 1999
Greetings
I'm clear about the use of a DELETE trigger to "move" your deleted record to a second database as a sort of recycle bin.
But SS7 has the limitation, and it's mentioned in BOL, that it cannot reference your TEXT, NTEXT or IMAGE fields in the DELETED table. It says to join the original table with DELETED to get at those fields.
The only problem is the original table's record has been deleted! Even though the transaction has not yet been COMMITTED.
Here's my Trigger:
CREATE TRIGGER AuditTest ON Activity FOR DELETE AS
INSERT AuditDB.dbo.Activity
SELECT Activity.* FROM Activity INNER JOIN Deleted
ON Activity.ActivityID = Deleted.ActivityID
And for discussion, here's my Table:
ActivityID uniqueidentifier
OrgId uniqueidentifier
Title varchar(600
Active bit
Comments text
LastUpdate datetime
Any suggestions? Has anyone been able to implement a DELETE Audit
Trigger on a table with BLOBs?
Thanks,
-Rich
Richard Hundhausen
Stuttgart, Germany
View 3 Replies
View Related
Dec 7, 2011
I have a requirement to audit tables in a SQL Server database. The tables are dynamically created when the application creates a form and the table holds the form data. So my plan is this, I have worked out the audit table (static) and the trigger. What i'm having issues with is getting the trigger to create from within the stored procedure. So just to recap: the user creates a form in the app, this creates a table and should call this stored procedure. The stored procedure creates the trigger on that table (which begins auditing that table, inserting to the static audit table based on the table name being passed into the stored procedure).
Where im at: I can create the stored procedure. When i go to run the stored procedure, I get the errors after passing the table as a value.
In my opinion it's an error with the correct number of single ticks, but not sure.
The Code:
USE [AdventureWorks]
GO
/****** Object: StoredProcedure [dbo].[spReplaceAuditTrigger] Script Date: 12/06/2011 15:28:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC spReplaceAuditTrigger( @PassedTableName as NVarchar(255) ) AS
[code].....
View 3 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
Jan 24, 2001
I have a update trigger on a table which fires trapping the userid and time in the same record. The code is below. The tirgger only works if I have the recursive triggers option unchecked on databae properties. Is the way I am trying the only way to accomplish this in the update trigger or is there an more efficient way. Thanks
CREATE trigger tr_cmsUpdt_MARS on dbo.PATIENT_MEDICATION_DISPERSAL_ for UPDATE as
-- updates record with sql user and timestamp
--created 11-28-00 tim cronin
DECLARE @muser varchar(35),
@rec_lock_status int,
@ptacpt_status int
set @muser = current_user
begin
UPDATE PATIENT_MEDICATION_DISPERSAL_
set MODIFIED_BY = @muser,
MODIFIED_TS = getdate()
from deleted dt
WHERE PATIENT_MEDICATION_DISPERSAL_.RECORD_ID = dt.RECORD_ID
end
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
View 2 Replies
View Related
Mar 31, 2008
Currently running Sql Server 2005Is it possible to issue the delete command and capture the affected rows asxml types that will be stored in an audit table with an xml column?Something along the lines of:delete from source_tableoutput(deleted.*into audit_table (xml_audit_column)for xml auto)where source_table.column = @delete_value
View 1 Replies
View Related
Jul 22, 2013
I have a 'searchpath' column in audit table. I need to extract the folders from the column and display it in separate columns.
Column searchpath has value like:
//content/folder[@name='AFR']/folder?[@name='AFRABC']/folder[@name='CDD']/folder[@name='Packages'] /folder[@name='Deployment']/package[@name='XXX spend name']/model
I need to extract the folder names and display it in separate columns in a new table this way:
AFR AFRABC CDD Packages Deployment XXX spend name
View 6 Replies
View Related
Mar 31, 2003
Hi,
I wanted to create a new trigger, but Enterprise Manager tells me about an "Incorrect syntax near @UpdatedByID, line 28". I double-checked everything, but it still does not work :mad: .
Any hints :confused: ?
TIA,
-Gernot
Here is the statement (line 28 is marked with ***):
CREATE TRIGGER TransferToABII ON [dbo].[CALGeneral]
FOR INSERT
AS
BEGIN TRANSACTION
BEGIN
DECLARE @Event varchar(255),
@BBaseUID int,
@StartDate smalldatetime,
@EndDate smalldatetime,
@Details varchar(255),
@AddressID int,
@ProjectID int,
@UpdatedByID int,
@ActID int,
@EventID int
SELECT @Event = Event,
@BBaseUID = BBaseUID,
@StartDate = StartDate,
@EndDate = EndDate,
@Details = Details,
@AddressID = AddressID,
@UpdatedByID = UpdatedBy,
@ProjectID = ProjectID
FROM INSERTED
BEGIN
EXEC BrainBase.dbo.BB_NEW_CREATE_NoteTask_Ret *** (@UpdatedByID,
@AddressID,
@ProjectID,
@BBaseUID,
@StartDate,
GetDate(),
@Event,
NULL,
NULL,
NULL,
NULL,
@Details text,
@ActID = @ActID OUTPUT,
@EventID = @EventID OUTPUT)
END
BEGIN
UPDATE CALGeneral SET ActID = @ActID WHERE ID = INSERTED.ID
END
END
IF @@ERROR <> 0
BEGIN
RAISERROR('Error occured',16,1)
ROLLBACK TRANSACTION
END
COMMIT TRANSACTION
View 4 Replies
View Related
Aug 4, 2005
Any idea where to look for?
thanks,
View 1 Replies
View Related
