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


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

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

Posted By:      Posted Date: August 21, 2010    Points: 0   Category :ASP.Net
 

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.




View Complete Post


More Related Resource Links

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?

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

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

Triggering an event to occur within a VB.NET app if something is inserted into an SQL Server table

  
Let's say SQL Server is inserting arow within a table. Is there a way to let VB.NET know that an event was fired (being the inserting of the row within the table), without the need to query anything. Bascially having SQL Server tell the VB.NET app that an event was fired. It seems like it might be possible using CLR and the .NET framework integration wthin SQL Server, but I haven't found a sample program showing what I'm attempting. I understand that this topic covers SQL Server and VB.NET.  This need being for both and this forum only set for SQL Server, I thought I'd take the risk and post in this forum. Thank you in advance for any assistance.Newbie

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'

Get inserted ids when inserting from User Defined Table Type

  
Imagine this procedure:   CREATE PROCEDURE [dbo].[insertActive] ( -- Add the parameters for the stored procedure here @p_data dbo.activeUpdate readonly) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. insert into dbo.active (name,stateid) select name,stateid from @p_data END How can I get the ids of the rows that have been inserted?  I tried searching the net and the forums but could not find anything. Thanks for any and all help, Gareth.

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

URGENT :Deleted Vlues to be inserted in new table

  

HI, I need some help from you guys ...Please

 

My requirement is

I have a database called DB

USE DB

 

SELECT COUNT (*) FROM CUSTOMER WHERE SSN NOT LIKE'88%"

----Result:8990

SELECT CUSTOMER_ID FROM CUSTOMER
WHERE SSN NOT LIKE '88%'

---(i will have 8990 Customer)IDs

 

DELETE FROM TABLE CUSTOMER

WHERE SSN NOT LIKE '88%'

and here i have to store the deleted results it in a new physical table ...but here is the issue

I have columns called Customer_ID and ORDER_ID ( THERE ARE 28 TABLES DEPENDENT ON CUSTOMER TABLE

Dependent Tables:  Fullfiullement( Customer_ID,Order_ID,Phone))

PERSONAL( CUSTOMER_ID,PHONE)

ADDRESS( CUSTOMER_ID,ADDRESS)

etc etc

dependent or referencing other tables ..so i have to DROP the Child table first with taking the Customer _ID and order_IDs from above ...

 

 

SO i need to write a Script which will delete the Child tables and then Parent tables and i have to Save each deleted tables Data ........

I will  have :Customer_ID   

SELECT CUSTOMER_ID FROM CUSTOMER
WHERE SSN NOT LIKE '88%'

---(i will have 8990 Customer_ID )

 

 

and i need to delete only the records of those customer_IDS

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...

 

 

 


What is causing oracle datbase table failing to save inserted Record permenantly?

  

I have a table in orcale database where I am inserting records. I am able to insert a record in a table but it is not being saved permenantly.When inserting there is no error. what could be causing this?


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'

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