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

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

SQL Trigger INSERT, UPDATE table name (inserted updated)

Posted By:      Posted Date: October 08, 2010    Points: 0   Category :Sql Server
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?

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?


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

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

SQL Checking the table before insert or update

I need your help with a SQL Stored Procedure PLEASE!!!GOAL: I need to check TableA for the ItemID if the ItemID Is Not found Insert a new record. If the ItemID Is found Update existing record.***********************Below is Incorrect***********************Please HELP!!!!IF @ItemID is not null          Set NewRecord =  Select ItemID FROM ContentItems Where EXISTS  (select ItemID from ContentForm_Fields where ItemID=@ItemID)IF NewRecord is not null   Update ContentForm_FieldsELSE Insert ContentForm_Fields

Trigger without UPdate,INSERT and DELETE


I have SQL SERVER 2005 and I have a table with a Cloumn DATETIME data Type,

I want to ceate a trigger that checks the table rows and delete the record (s) when it has (DATETIME colume value - today date()) >20  days.

Is it possible to write a trigger for that? 

I am new to SQL Server and trigger.




Why deadlock occurs using INSERT/UPDATE in a table with 2 sessions



I am working on a problem in my odbc application, which i have isolated to the MS SQL Server query analyzer.

The problem is related to the deadlock faced by one of the session, when 2 transactions each running from 2 sessions are trying to INSERT a record from their respective session with the duplicate values for the columns icol1 and cCol2 and then one of the session UPDATE the value of column icol1 in the previously inserted record. After the completion of the test, I see 1 record created in the table from one of the session, but other session becomes deadlock victim.

Following is the table structure:

CREATE TABLE [sgarg].[test_buf](

[icol1] [int] NULL DEFAULT ((0)),
[ccol2] [varchar](30) NULL,
[ccol3] [varchar](30) NULL,
[RECID] [bigint] NULL

Following are the index definitions

CREATE UNIQUE NONCLUSTERED INDEX [test_buf##ixprim] ON [sgarg].[test_buf]
 [icol1] ASC,
 [ccol2] ASC

CREATE NONCLUSTERED INDEX [test_buf#_#recid] ON [sgarg].[test_buf]

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'

SSIS : Insert, Update, Delete records from destination table by comparing records in source table.


Requirement: Inse

rt, Update, Delete records from destination table by comparing records in source table.

Update: if records exist in both the table compare them, and update value in destination table if value is different.

Insert: if record doesn't exist in destination table, add new record in destination table.

Delete: if record exist in destination table but not in source table, delete record from destination table.

----> I have created an SSIS package that does all this task but in order to perform delete operation, I have to store all records of destination table into a temporary table.
And since it's not the most efficient way, I have created another SSIS package by using Lookup & conditional Splits
method, now it works fine with inserting and updating information but I am not sure on how to handle removing record operation!

I would really appreciate if you could help me with this.

(In order to archive inserting and deleting records, I have referred following thread!
http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspx )

Help required regarding audit (Update/INSERT/DELETE) a table in Database.


Hi all


Help required regarding audit (Update/INSERT/DELETE) a table in Database.



I have table having with imp Data lets say ID, some of users are setting ID's to their own values with out informing us. I know it is not possible using Application what every my Database using.


Now I want find that who is doing this


Need suggestion on Mutiple table insert and update in DAL


I have two datatable and its own table adapter  and both are realated . What is the best practice to  update or insert  both table . . Any ideas

SQL Update and Insert: Inserting a whole data table into another table



If I create a data tabe in C# like so:

datatable t = new DataTable;

DataColum C1 = new DataColum;//ID column


//the same with column C2

Then I fill up the table with some data.

Then the goal is to either insert this data or update data in an SQLtable in SQL. If the data is inserted, the ID column in t would be empty, if updated, it would not be. So in the insertion case, the sql query needs to make sure that unique IDs are assigned during insertion, in the update case, it just has to look for the right IDs.

How to go about this?

Asking because I am only familiar with updating and inserting single lines into a table. One could loop in C# over the datatable t of course and insert or update line by line, but this would probably be slow. Is there a way to write an SQL query like this somehow:

Insert Into SQLTable Values (@t)


And Update like this:

Update SQLTable SET ... Where C1 = @t.C1

Or something like that.

I know that SQL server can create automatic update and insert queries and that you can then type things in C# like


But, this has proven to be super slow on our server, for unknown reasons. I have asked on this forum and got some answers from experts, but was not able to srot out the problem.

How can we write

How to Insert - Update - Create articles to tbl_Articles table by using FCKeditor ASP.Net MVC


I read many post about Intergrating Fckeditor to ASP.Net MVC, and I have done.

Now, I have tbl_Articles table, it contain some articles... How to Insert - Update - Create articles from TextArea of FCKeditor (ASP.Net MVC)

Please give some way for me. Thanks.

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

How do I automate an UPDATED datetime column on insert/update

Automatically setting a CREATED column in a table is when a record is inserted is simple (define GETUPDATE() on it). Is there a simple way of doing this for an UPDATED datetime column whenever the record changes (insert/update)?

How to archieve the field template that can update or insert multiple field value in database table,



How to archieve the field template that can update or insert multiple field value in database table,please? 

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