.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

calling a trigger after insert sp

Posted By:      Posted Date: October 06, 2010    Points: 0   Category :ASP.Net

i want  to create a trigger after i insert record in table1 and in trigger there will be an update sentece which updates x column in table2 


i have a stored procedure like

insert into Table1(a,b,c) values(1,2,3)

select @@identity


my trigger should update table 2

update table2 set x=5 where id=@a

@a parameter must come from my insert stored procedure's select @@identity line


how can i create a trigger like this? this will be my first trigger so i dont know anything about them

after creating trigger i think i should control if it execute successfully or not in my insert stored procedure so if it works successfully my insert is ok but if trigger doesnt work my insert sentence shouldnt work


could anyone help me about that?




View Complete Post

More Related Resource Links

How to avoid network problems while calling a webservice in a SQL CLR trigger?

Hi All, My Goal: DB synchronization between SQL server 2005 and Mysql database via web services. I have created a SQLCLR trigger, in which i'm calling a web service to sync/update remote (MYSQL) DB over a specific constraints. After getting the acknowledgment from web service i'm updating the sync flag to success (In SQL SERVER2005). If any network delay happens i am unable to know whether sync has done successfully or not. How to avoid network dependency here? Is there any reliable queuing mechanism available in sql server 2005 to eliminate dependency over network? I am looking any suitable service/approach in sql server 2005 that can take care of calling web services as asynchronously and update the status of sync flag irrespective of network. Thanks in advance if anyone provide the good approach as step by step in detail.

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'

MS Sync Framework INSERT Trigger

Hello, Due to the sync framework insert trigger my business logic was broke. I was getting resulted in a StaleStateException (-1 rows affected, expected 1). error. I looked at the sync insert trigger and found a strange thing. There is an update call before inserting to the tracking table. This update call joins with the tracking table for inserted record. How it is going update the record which really do not exists! I removed the UPDATE call and it works fine now. However I am not sure the reason behind the presence of the UPDATE call withing the INSERT trigger. Here is the sample trigger: ALTER TRIGGER MYTABLE_insert_trigger] ON MYTABLE FOR INSERT AS UPDATE [side] SET [sync_row_is_tombstone] = 0, [local_update_peer_key] = 0, [restore_timestamp] = NULL, [update_scope_local_id] = NULL, [last_change_datetime] = GETDATE(), [ID] = [i].[ID] FROM [sync].[MYTABLE_tracking] [side] JOIN INSERTED [i] ON [side].[PrimaryKey1] = [i].[PrimaryKey1] AND [side].[PrimaryKey2] = [i].[PrimaryKey2] INSERT INTO [sync].[MYTABLE_tracking] ([i].[PrimaryKey1], [i].[PrimaryKey2], [create_scope_local_id], [local_create_peer_key], [local_create_peer_timestamp], [update_scope_local_id], [local_update_peer_key], [sync_row_is_tombstone], [last_change_datetime], [restore_timestamp], [i].[ID]) SELECT [i].[PrimaryKey1], [i].[PrimaryKey2], NULL, 0, @@DBTS+1, NULL, 0, 0, GETDATE(), NULL, [i].[ID] FR

After INSERT Trigger question - how to use value from last added record

Hi, I have a table where one of the fields is defined like [barCodeID] int IDENTITY(1, 1) NOT NULL - so the value in this column is generated automatically. What I want is, after each INSERT, to write this automatically generated value to another table. The simplest would be to do it with an after insert trigger, but don't know how. How to refer to a value from the last added record in a trigger? Thanks

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

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.




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?

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 :)

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





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,

/* Sélectionner le nombre de but inscrit par un joueur */

/* Sélectionner le résultat avant tout changement de l'équipe visiteur */
    join SOM

Insert value using Table Value Functions

a real gem in Sql Server 2008. mostly people still using Stored procedure may be they shifted to SQL Server but they are not using TVF right now.

Insert Rows with a GridView

In ASP.NET version 1.x, I used the footer on the DataGrid control to insert new rows by placing a series of TextBox, DropDownLists, CheckBoxes etc. controls on it and then handling a save button. This also works with the GridView in version 2.0 but with one major exception: if your data source is empty, the GridView will hide your footer (and header) and only display the EmptyDataText.

SqlCommand.ExecuteNonQuery() returns -1 when doing Insert / Update / Delete

Sometimes you end up with a return value of -1 when using the SqlClient.SqlCommand.ExecuteNonQuery method.

Why is that?

Well, the ExecuteNonQuery method is there for statements for changing data, ie. DELETE / UPDATE /INSERT, and the returned value are the number of rows affected by that statement.

When checking the documentation we can see that there are some conditions that return -1.

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command.

When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of

rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

Insert Error not helpful



I am using VS2010

When I try to updated or insert a record in my User table I am getting an error that does not make sense to me and I cannot figure out what is wrong.

Also, when I try to insert a new record I get a validation error saying that the Id is required. However it is an identity field in the db so it should not be necessary to send it to the server.

Could you please shed some light?


An error has occurred.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: Microsoft.Web.UI.WebControls.EntityOperationException: An error has occurred.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. 

Stack Trace:

[EntityOperationException: An error has occurred.]
   Microsoft.Web.UI.WebControls.DomainDataSourceView.PerformCudOperation(ChangeSetEntry operation, Action`1 operationPerformingEvent, Action`1 operationPerformedEvent) +331
   Microsoft.Web.UI.WebControls.DomainDataSourceView.UpdateObject(Object oldEntity, O

Detecting Edit or Insert Page


I am coding a .cs file that handles the metadata for a particular table. I am putting a custom validation that allows me to update or insert only if the PubMedID is not already in the database. This would be easy except that the PMID should also be allowed to be null as some of the references in the database were not from PubMed. So i perform a query on the database to see if there is already an identical PMID in the database. This works great when using Insert. However, when using Edit, it does not. The PMID is already in the database because the one currently being edited has the same PMID!!! So I know exactly how to change my query etc. However, I do not know how to tell from inside this Edit/Insert agnostic page, which one it is being called from. Thanks for your help! 

Using a trigger or anything else to populate two tables


Hi! I'm creating an application that's supposed to first add a record to table1, and then get the ID from that record to use when adding a record to table2, to be able to associate these two records with eachother.

The user gets to type in some values that goes to table1, and some values that goes to table2, but before the insert statement for table2 is executed i need the ID from the recently added record in table1. Some dude told me to use a trigger for the autopopulate purpose, but does that really work when i also need to save some values that's user input, and when those values doesn't get saved in table1?

Are there any other way to do this or can i send values to a trigger? I'm new to triggers and stored procedures, i don't have any particular knowledge of this, any help is appreciated!


Regards, Monsterbadboll

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?


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