.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Kaviya Balasubramanian
Imran Ghani
Post New Web Links

Generic Audit Trigger CLR C#(Works when the trigger is attached to any Table)

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
 
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 Contextstring TName; //Where we store the Altered Table's Namestring User; //Where we will store the Database UsernameDataRow iRow; //DataRow to hold the inserted valuesDataRow dRow; //DataRow to how the deleted/overwritten valuesDataRow aRow; //Audit DataRow to build our Audit entry withstring PKString; //Will temporarily store the Primary Key Column Names and Values hereusing (SqlConnection conn = new SqlConnection("context connection=true"))//Our Connection{conn.Open();//Open the Connection//Build the AuditAdapter an


View Complete Post


More Related Resource Links

how to get id value from the row inserted in a table with thr trigger.

  

I have a reports table with column report id and and report version. I created a trigger on this table to execute an application to do some processing each time a row is inserted in reports table. I need to pass the new report id execute this application from the trigger. How do I get the report id from the row that was inserted?


Thanks.


execute update timedout expires , table with trigger due to recovery of database

  

I have table A in database A  , in table A i have trigger to insert record to database B table A, each update and insert of database A -table A, triiger fire and insert record in database B table A, when i try to update some times asp.net application gives error

"Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."

and then error log says System.Data.SqlClient.SqlException: Database 'database B is being recovered. Waiting until recovery is finished.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean ret

Trigger that deletes another table's row

  
Hi, I'm new to making triggers. I have the following trigger wich should delete a row in the Activity_Log table when I delete a row in the Poll_Comments table.... I have an error in my where clause - because it don't delete the specific row.... My trigger:ALTER TRIGGER [dbo].[deletePollCommentsTrigger] ON [dbo].[Poll_Comments] FOR DELETE AS BEGIN DECLARE @intRowCount int SELECT @intRowCount = @@RowCount IF @intRowCount > 0 BEGIN DELETE Activity_Log WHERE id IN (SELECT questionId FROM deleted) AND activityDateCreated IN (SELECT commentsCreateDate FROM deleted) AND activitySectionPId = 8 END END If I change the statement to: DELETE Activity_Log WHERE id IN (SELECT questionId FROM deleted) AND activitySectionPId = 8 It correctly deletes all rows with that specific questionId and where activitySectionPId = 8... But I want also to have a where clause on, that ask for commentsCreateDate = activityDateCreated - so I only delete one row and not all rows... How can I do that? Kind regards,simsen :-) 

Trigger not firing on second table.

  
Im wondering if someone can help me. I have created 3 simple tables. The 1st table has a trigger that inserts a record into the 2nd table. The second table has a trigger that inserts into the 3rd table. When I insert a row into the 1st table the 1st trigger on this table fires but the trigger on the 2nd table does not fire.  In the triggers i have two print statements which should print out text when the trigger is firing but I never see the 2nd trigger firing. Stepping through with the debugger in SQL 2008 I am also not able to F11 into the 2nd insert statement. Im left scratching my head on this and was wondering if anyone could help here. Thanks in advance. I have included a sampel script to replicate the porblem.     CREATE TABLE [dbo].[TriggerTest1](     [Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,     [Value1] [int] NULL,     [Value2] [int] NULL,  CONSTRAINT [PK_TriggerTest1] PRIMARY KEY CLUSTERED (     [Id] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY] CREATE TABLE [dbo].[TriggerTest2](     [Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,     [Value1] [int] NULL,     [Value2] [int] NULL,  CONSTRA

After insert trigger on the same table.

  
Hi! i have the following table. CREATE TABLE [dbo].[EICANALESRES]( [IdContrato] [decimal](10, 0) NOT NULL, [EiCanalId] [decimal](10, 0) NOT NULL, [EiFechServ] [datetime] NOT NULL, [EiFechMaq] [datetime] NOT NULL, [EiFechCorr] [datetime] NOT NULL, [EiValorFis] [decimal](25, 4) NOT NULL, [EiValorNum] [decimal](25, 4) NOT NULL, [EiValorCalc] [decimal](25, 4) NOT NULL, [EiEst] [smallint] NOT NULL, PRIMARY KEY CLUSTERED ( [IdContrato] ASC, [EiCanalId] ASC, [EiFechServ] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]  I want a new record that takes all the data from from the recently inserted record where idcontrato =2 and eicanalid =91. And then give this new record a new idcontrato. Without deleting the original record. i have this so far, but it erases the original record.   alter TRIGGER [dbo].[datosdemo] ON [dbo].[EICANALESRES] AFTER INSERT AS BEGIN SET NOCOUNT ON; delete from eicanalesres where idcontrato = '13' and eicanalid = '91' and eivalorfis = '0' update dbo.eicanalesres SET idcontrato = '13' FROM INSERTED WHERE eicanalesres.idcontrato = inserted.idcontrato AND eicanalesres.eicanalid = inserted.eicanalid and eicanalesres.eifechserv = inserted.eifechserv and inserted.idcontrato = '02' and inserted.eicanalid = '91'

how can insert only new record into another table through trigger

  
I have on master table in sql in which every time new data is inserted.i just want to copy the new inserted record into another table.How can i do this. below is my trigger but how can it identify only new records should be inserted into second table.please help me and modify it. CREATE TRIGGER CopyRecordToPortInTable ON SmsTable AFTER INSERT AS BEGIN SET NOCOUNT ON; insert into Portintable(smsno,smstext,flag) select (sms,smstext,0) where condition END

Save Changes not working correctly when SQL table has a trigger

  

Hi all,

My WCF isn't working currently when a SQL table has a trigger on it that updates a column.

My table:

(SalesID int, SalesNumber nvarchar(10), OrderType nvarchar(3), OrderDate datetime) the trigger creates a new formatted string value for SalesNumber based on the OrderType and the SalesID, e.g GRN00001

The trigger is applied when the WCF service adds a new record. But the return values for SalesNumber is still NULL. I'm not sure why it will not update. If I restart the programme it retrieves the correct data...

public tblSales tblSales_Save(tblSales dsUpdated)
{
  db.tblSales.ApplyCurrentValues(dsUpdated);
  db.SaveChanges();
  return dsUpdated;
}

public tblSales tblSales_Save(tblSales dsUpdated)
{
  db.tblSales.ApplyCurrentValues(dsUpdated);
  db.SaveChanges();

  tblSales dsNew = tblSales_FindID(dsUpdated.SaleOrderID);
  return dsNew;
}


Neither way works...

 

 

 


Save Changes not working correctly when SQL table has a trigger

  

Hi all,

My WCF isn't working currently when a SQL table has a trigger on it that updates a column.

My table:

(SalesID int, SalesNumber nvarchar(10), OrderType nvarchar(3), OrderDate datetime) the trigger creates a new formatted string value for SalesNumber based on the OrderType and the SalesID, e.g GRN00001

The trigger is applied when the WCF service adds a new record. But the return values for SalesNumber is still NULL. I'm not sure why it will not update. If I restart the programme it retrieves the correct data...

public tblSales tblSales_Save(tblSales dsUpdated)
{
  db.tblSales.ApplyCurrentValues(dsUpdated);
  db.SaveChanges();
  return dsUpdated;
}

public tblSales tblSales_Save(tblSales dsUpdated)
{
  db.tblSales.ApplyCurrentValues(dsUpdated);
  db.SaveChanges();

  tblSales dsNew = tblSales_FindID(dsUpdated.SaleOrderID);
  return dsNew;
}


Neither way works...

 

 

 


how to fire trigger and save it to another table?

  

I want to create a trigger for insert and whenever i insert the data the trigger should fire and notify some value inserted i want to create log table to save this information,now i have created a trigger but i dont know how to map it to log table to notify the changes??i want some idea about this thanks.


In a trigger can up insert data into a table located on a linked server?

  

EXEC master.dbo.sp_addlinkedserver @server = N'ServerB', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ServerB',@useself=N'False',@locallogin=NULL,@rmtuser=N'lnklogin',@rmtpassword='########'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'rpc out', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'query timeout', @optvalue=N'0'

SQL Trigger INSERT, UPDATE table name (inserted updated)

  
I am trying to make a trigger for INSERT, UPDATE.  For INSERT you get a table named inserted that you can use, and for UPDATE you get updated.  But what is the name if you doing a trigger for both?

Auto udating data in multiple table through trigger.

  

table a(

sno IDENTITY int(1),

name varchar(5),

roll_no numeric(5)

sum more columns

)

table b(

sno IDENTITY int(1),

name varchar(5),

sum more columns

)

table c(

sno IDENTITY int(1),

roll_no numeric(5)

)

 

Could any one kindly tell me how can i do it.

whenever data is entered in table a the values of name and roll_no to be automatically filled in table b and table c with use of trigger.


trigger on insert is failing for data insert in a table

  

hello experts,

here is some question for you and i'm sure you guys will have awsome solution

i have a table with companyid and tranid as a primary key. there is a insert trigger on it and the trigger insert property is instead of insert, means data is manually inserted thru trigger.

in trigger, i check if companyid and tran id exists in the table or not, if yes then i add a record in another table (history table/duplicate table) and if not found then i add record in this table.

this stuff work perfectly if i work in ssms and it is all good but when i work thru ssis, it gives me following error and it clearly showing that it is because of pk

[Insert Data in EFT [46]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "The statement has been terminated.".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Violation of PRIMARY KEY constraint 'PK_Mail_Queue'. Cannot insert duplicate key in object 'dbo.EMail_Queue'.".

any solution


mark it as answer if it answered your question :)

how to use DML update trigger to track all table changes

  

Hi All,

Somebody is making updates to a table that shouldn't be updated!  I've been asked to track which user, the updated column name(s), and the before and after value(s).  It doesn't matter how many or which field(s) had the value updated.  If it was updated, we need to track it.

The straightforward method seems to be using a DML update trigger like the example shown at http://msdn.microsoft.com/en-us/library/ms187326.aspx, but there's over 30 columns in the table that need to be watched.  From the example at that page, it appears I would need a "IF UPDATE(my_col_name)" statement for each and every column we'd like to watch.  Correct?

How would we ID the user that made the change?  I assume there's a variable available within an update trigger that holds that value.

My main question ends there, but here's a little more background that may affect any advice:

1.  We can't track changes in a table.  Instead, they have to be written to the SQL Log, which I've found can be accomplished using "xp_logevent".
2.  A separate log entry for every field updated in a row is not a requirement.  One log entry with a row showing the old values and another row showing the new values would suff

Data from the server subscription cannot trigger table trigger

  

Publisher  Server A  (Job  table) no trigger

Subscription Server B (Job table) has trigger

Data from the Server A subscription cannot trigger Server B Job table trigger


Trigger on a subscriber table

  

Hi there,

I have a SQL 2008 Merge publication - at ONE of the subscribers, I want to create a trigger which will send off an email using Database Mail in certain situations.


Can I create the trigger on the appropriate table in that subscribers database ?

 

thanks
Bruce

 

 


the insert trigger works properly, the delete trigger does not.....what do you think

  

INSERT TRIGGER

ALTER TRIGGER [dbo].[CREER_SOMMAIRE_PARTIE_JOUEUR] ON [dbo].[SOMMAIRE_PARTIE]

FOR INSERT
  as

DECLARE     @NB_BUT int,
            @RESULTAT_VISITEUR int,
            @ID_EQUIPE_VISITEUR int,
            @ID_EQUIPE_LOCAL int,
            @ID_EQUIPE_JOUEUR int,
            @RESULTAT_LOCAL int,
            @NB_PASSE int,
            @NB_MINUTE_PUNITION int,
            @NB_BUT_TOTAL_JOUEUR int,
            @NB_PASSE_TOTAL_JOUEUR int,
            @NB_MINUTE_PUNITION_JOUEUR int


/* Sélectionner le nombre de but inscrit par un joueur */
SELECT @NB_BUT = NB_BUT
FROM inserted SOMMAIRE_PARTIE

/* Sélectionner le résultat avant tout changement de l'équipe visiteur */
SELECT @RESULTAT_VISITEUR = RESULTAT_VISITEUR
FROM PARTIE
    join SOM

Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend