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

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

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

Posted By:      Posted Date: September 15, 2010    Points: 0   Category :Sql Server
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

View Complete Post

More Related Resource Links

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

How to check if record is added in access database



I'm trying to put a registration page together in Visual Web Designer.

I would like to send a confirmation Email after someone is leaving his registration data and pressing the INSERT button.

The key fields in my database are: Email, Name, Surname, Address

Before I send the confirmation Email I would like to check if the email value entered in the form is realy added to the database.

Please for help in this matter. I was copy past already some C code in my page, therefore it will be fine if the code can be in C.

Again, I need help to:

- checking if record is realy added to database after pressing INSERT button

- setup the confirmation Email

Thank you in advance.


Insert Same Record Multiple Times.

I have a simple form that inserts a record into a SQL table. The fields are: SerialNumTagNumPONumLocationAddressDescription   This works great. However, I have now been asked to have the form insert x number of records at one time. They want to be able to fill out the following fields: PONumLocationAddressDescription and then have texbox where they can add a number and have that number of the same record inserted.    I hope this makes sense.

How do I show the last record added when using FormView?

hello all, I am using FormView to display one record at a time and after I add a new record, it won't display the last recorded added.How can I force FormView to display the last record added? Thank you  

Cannot INSERT record using Asp.Net Dynamic Data Domain Service Web Applications

 Hi, I am working on a 'Asp.Net Dynamic Data Domain Service Web Application' project using just 2 tables, FAQ_Categories and FAQs. The FAQs table has a foreign key to the FAQ_Categories. The steps are straight forward. Add a new project (Asp.Net Dynamic Data Domain Service Web Applications) Add folders Model ( for Linq2Sql datacontext), Domain (for the Domain Services class) Add Linq2Slql and add the 2 tables to the designer. Build the project. Add the Domain Service Class to the Domain folder. in the wizard select the DataContext, uncheck the Enable Client & Expose ODate checkbox Check bothe table & their corresponding Enable Editing tables checbox Check the Generate Metadata class Click OK. In Global.asax set Scaffold to true and add the DomainService name. Build and run.   Pages List and Detail are working fine. But if I try to insert a new record, I get the error "FaqId is required" Faq_ID is the primary key in the database table and it is set to Identiy. The Datacontext is correct, it marked the FAQ table with <Global.System.Data.Linq.Mapping.ColumnAttribute(Storage:="_CustomerId", AutoSync:=AutoSync.OnInsert, DbType:="Int NOT NULL IDENTITY", IsPrimaryKey:=true, IsDbGenerated:=true)> _   I tried this using Linq2Sql, Entity Framework, And both in VB and C# with the same result. I am using VS20

How to insert a Record as per collections

Table1 valuescol1             col2---------------------------1                abc2                def3                ghi Table2 valuescol1     col2   col3   col41           1      gh     jk-----------------------------------------Now the Problem is in My coding i taken List<string> li=new List<string>();li.Add("abc");li.Add("def");li.Add("ghj");in a Textbox i am entering abc value then it should get it ID value.My Questions areHow should i assign a value of Col2 value of Table1 as per the data values what we are entering ?User is entering "abc" or "def" or "ghj" only not ID values.....What is the code for that?

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'

Display Inserted Record After Insert Using A FormView

How do I display a record in a FormView once the record has been inserted? I'm able to get the Identity of the new record without anyproblems, but I can't figure out how I should code the _ItemCreated event. Protected Sub FormViewMessageDetail_ItemInserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewInsertedEventArgs) 'Refresh the list to show the new record RepeaterMessages.DataBind() 'Set the .SelectCommand to Select the New Record SqlDataSourceMessageDetail.SelectCommand = "SELECT * FROM Messages WHERE [Id] = " & NewId FormViewMessage.DataBind() FormViewMessage.ChangeMode(FormViewMode.Edit) test.Text = NewId.ToString End Sub Protected Sub SQLDataSourceMessageDetail_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) NewId = e.Command.Parameters("@NewId").Value End Sub

MSMQ Trigger Question

Hi    I am developing a C#  application (Framework ver 3.0) using MSMQ Queues (target windows 2003 server). To quickly summarize, the application first checks for the presence of a specific private queue and will create it if it doesn't exist. If it exists, it will write the information to the queue. What I need to do programatically is set a trigger and rule to run an EXE to manage the created QUEUE. Since we don't know all the queues at design time and also want to eliminate setup issues if we did ,manual queue trigger setup is not an option. I haven't been able to find any guidance or examples on the web for doing this, can someone point me in the right direction.   Regards,    Jim

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

DDL Trigger Question - ADD_ROLE_MEMBER - Error regarding synchronous trigger registration

I am getting the following error when trying to create the DDL trigger below.  Why? Msg 1082, Level 15, State 1, Procedure DDL_tr_wb_Add_DB_Role_Member, Line 7 "ADD_ROLE_MEMBER" does not support synchronous trigger registration. Create Trigger DDL_tr_wb_Add_DB_Role_Member ON DATABASE FOR ADD_ROLE_MEMBER AS DECLARE @data XML SET @data = EVENTDATA() Insert Into Security_Audit ( PostTime, LoginName, UserName, [HostName], [Application], [Event], [Command] ) VALUES ( GETDATE(), -- The time of the event CONVERT(nvarchar(100), SYSTEM_USER), -- Current Login CONVERT(nvarchar(100), CURRENT_USER), -- The database user against whose session the event occurred CONVERT(nvarchar(20), HOST_NAME()), -- The workstation name CONVERT(nvarchar(100), APP_NAME()), -- Application name of the current session @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), -- The type of event @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') -- The Transact-SQL command that comprised the event ) Thanks,   Dave

Possible to send an email if a record is added to a table?

I would like to be able to send an email notification if a website user inserts a new record in a table.  This will be a relatively rare occurrence so it would be very useful to have the notification as opposed to having my users check my summary page that lists all the IDs that have a record in this table. I googled but I must not be hitting on the right keywords or phrase to find the answer to this question. Thanks in advance for 1) letting me know if this is possible (I'm version ASP.NET 2.0 and using VB with a SQL Server database) and/or 2) pointing me in the right direction to find out how to do it if it is possible. Thanks! 

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.




record count for subquery which is used for INSERT

My current code is like this

Insert into ReportDetail( Partcipantid, Reportid)

      select distinct ParticipantID , 3 from abc   where

        closed = 0 and ( isnull ( primaryphone, '' )

How to refresh page and dropdown after insert record from popup page


I have a page that is opened in a popup window from the main page. Once i click insert it pops up a message that says you have successfully added blah blah. Then once you click ok it closes the popup page and goes back to the main page. I want to be able to refresh the main page back to how it was when you first land on the page. Here is the code for my insert button:


 <asp:button id="insertButton" 
                    style="background-image:url('Images/Button.gif'); text-align:center;background-color:Transparent; cursor:hand; background-repeat:no-repeat; background-position:left; padding-left:15px; font-family: 'Arial Unicode MS'; font-size: small; font-weight: bold;" 
                    Runat="server" Height="25px" BorderStyle=None ForeColor=White    Text="Insert" 
                    Width="99px" CssClass="style13" CommandName="Insert" OnClientClick="javascript: alert('You have successfully added a New Donor!!');window.open('','_self','');window.close();" />


calling a trigger after insert sp


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?




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'

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