.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

After insert trigger on the same table.

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

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

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

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.

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

Need to add value to table on insert



I'm fairly new to .NET.  I need to add values CreatedBy, CreatedDate, ModifiedBy, ModifiedDate to the record before inserting it to the database. 

Please see code below. 

     [MetadataType (typeof(GRIPSMetaData))]
    public partial class SchoolDistrict
        internal void OnSaving(ChangeAction changeAction)
            if (changeAction == ChangeAction.Insert)
              this.CreatedBy = 5;
              this.CreatedDate = DateTime.Now;
            else if (changeAction == ChangeAction.Update)
                this.CreatedBy = 5;

How to setup db diagram to automatically insert username into table?

 I have a table setup in the database for users who are basically going to create a classified listing.  How can I setup a diagram that would easily create a table relationship with the Users table, so that it automatically inserts the users username when they submit a new ad?  If so, how?  I've tried adding a UserName row to my table and creating a relationship with the Users table "UserName" row, but it wasn't accepting the relationship when I tried to save the diagram...apparently because the UserName row in the Users table isn't a primary key?Also, one this is setup, what's the best way to provide the user a way to see a list of their ads and have the ability to edit them?

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

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

reading xml data and insert into database table

hi friends, .very urgent for me so only i send following script i create for read xml data and to import to table. i got result for reading xml and rows are added to table but the values are showing null pls clarify this problem. thanks alter procedure pizza_sales ( @xml xml ) as begin INSERT INTO pizza_xml (LOC_CODE, CUST_CODE, PRODUCT_ID, INV_TYPE, INV_NO, INV_DT, INV_QTY_IN_INV_UOM, INV_RATE_IN_BS_CURR, INV_VU_IN_INV_CURR, -- TOTAL_DISC_IN_BS_CURR, NET_INV_VU_IN_INV_CURR, NET_INV_VU_IN_BS_CURR, TOTAL_TXES_IN_BS_CURR ) select table1.column1.value('@LOC_CODE','nvarchar(75)'), table1.column1.value('@CUST_CODE','nvarchar(75)'), table1.column1.value('@PROD_CODE','nvarchar(75)'), table1.column1.value('@INV_TYPE','nvarchar(50)'), table1.column1.value('@INV_NO','nvarchar(100)'), table1.column1.value('@INV_DT','datetime'), table1.column1.value('@INV_QTY_IN_INV_UOM','numeric(28,8)'), table1.column1.value('@INV_RATE_IN_BS_CURR','numeric(28,8)'), table1.column1.value('@INV_VU_IN_INV_CURR','numeric(28,8)'), --table1.column1.value('@TOTAL_DISC_IN_BS_CURR','numeric(28,8)'), table1.column1.value('@NET_INV_VU_IN_INV_CURR','numeric(28,8)'), table1.column1.value('@NET_INV_VU_IN_BS_CURR','numeric(28,8)'), table1.column1.value('@TOTAL_TXES_IN_BS_CURR','numeric(28,8)') from @xml.nodes('pizza/pizzaxml')as table1(column1) end declare @id

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

create and insert into a dynamic sql table at runtime

Below I have a bunch of SQL statements that creates a table at runtime and the "pic" column is a dynamic column and the code creates the number of "pic" columns depending on the count of the uploader control.  the problem comes when I try to insert into that table and I am trying to say for every "pic" declare variable @pic.... it works well for just one file in the uploader but for multiple files i get the following error The name "pic1" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.Directory.CreateDirectory(MapPath(".") & "\port\" & clientname.Text & "\")        Dim objConn As New SqlConnection("Data Source=xxxxxxxxx.hostedresource.com; Initial Catalog=mrpoteat; User ID=xxxxxxx; Password=xxxxxxxxx;")        objConn.Open()        Dim strCommandText As String = ""        For index = 1 To Attachments1.Items.Count Step 1            If String.IsNullOrEmpty(strCommandText) Then                strCommandTex

How to insert the Current date and time in to SQL Table..

Hello Members,              I have create the table as per the following ..create table company(  empname varchar(30),  empid int,  joindate smalldatetime)I tried,insert into table values('Kumar',202, ??????)I want to insert Current date and time into table Company.....Please give me the solution...Thanks.. 

pull data from table A and insert into the same table

Hi All, When I pull data from one table and try to insert into the same table, is it possible for any erroneous data loading? ex:   Insert Into TableA Select Cat,Bank, City, Act From TableA A CROSS APPLY (SELECT COUNT(ACT) AS TOT from TableA ) B   Thanks        shamen

How to insert values into my table the data is coming from inline table valued function

Hi all,   i have table oi want insert values into my table the data is coming from inline table valued function how i can use the inline table valued function to insert datainto my table i have inline function which out puts 3 collumns of data how i can insert this data into my table i have the insert statment like below insert into tablename(c1,c2,c3,c4,c5) select ('abc',....................,'xyz') how i can use the out put put of my inline function to  to insert data into my columns c2,c3,c4. please help me out Thanks & Regards Sunil Yoganna

insert into dynamic SQL Table at runtim

the follow ing is supposed to create a table and insert the columns into the table at runtime.  It also has picture column and creates the amaount of picture columns based on the amount of files in the multiple file uploaer.. it works fine with just one file in the uploader.  The problem comes when there  are two or more files in the uploader and intead of adding the second image right after the first image is added, it instead adds the first image then goes back and trys to create the table again, and i get an error saying the table already exsits.I want it to add the second image right after the first image, not go back and create the table then add the second imageDirectory.CreateDirectory(MapPath(".") & "\port\" & clientname.Text & "\")              Dim objConn As New SqlConnection("Data Source=xxx")        objConn.Open()        Dim strCommandText As String = ""        For index = 1 To Attachments1.Items.Count Step 1            If String.IsNullOrEmpty(strCommandText) Then                strCommandText = "
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