.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

when does the trigger gets fired?

Posted By:      Posted Date: September 25, 2010    Points: 0   Category :Sql Server

Assume a trigger tr1 was written to fire on insert , and to insert datetime value to a update_date column,

does the trigger fires

1. when the insert event was called on that table

2. duing the insertion to the table when teh control looks for the value in teh update_date column?



View Complete Post

More Related Resource Links

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?


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

Trying to set update panel trigger programmatically


 I have a recursive sub that finds a dropdown list and sets up an AsyncPostbackTrigger for an update panel. I have tried calling this sub both from page_init and page_load 

If the dropdownlist is within the update panel, eveything works fine. If the dropdown list is not in the updatepanel I get error

A control with ID 'ddlPersonFilter' could not be found for the trigger in UpdatePanel 'up1'. In both cases the sub does find the dropdown list and executes the code to set up the trigger. Thanks for your help.

    Private Sub SetUpdatePanelTrigger(ByVal oControl As Control)
        For Each ctrl As Control In oControl.Controls
            If TypeOf ctrl Is DropDownList Then
                If CType(ctrl, DropDownList).ID = "ddlPersonFilter" Then
                    Dim thisddl As New DropDownList
                    Dim thistrigger As New AsyncPostBackTrigger
                    thisddl = ctrl
                    thistrigger.EventName = "SelectedIndexChanged"
                    thistrigger.ControlID = thisddl.ID
                    Exit Sub
                End If
                If ctrl.HasControls Then
                End If
            End If

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

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.

CLR Trigger with stored procedures - transaction error

Hi, I've developed sample CLR trigger which call stored procedure but if execution of stored procedure fail I got following exception The context transaction which was active before entering user defined routine, trigger or aggregate "CskTest" has been ended inside of it, which is not allowed. Change application logic to enforce strict transaction nesting. The statement has been terminated.  My trigger [SqlTrigger(Event = "FOR INSERT", Name = "CskTest", Target = "Requirement")] public static void HandleRequirement() { using ( SqlConnection conn = new SqlConnection("Context Connection=true") ) { SqlCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "Test"; conn.Open(); try { cmd.ExecuteNonQuery(); } catch ( Exception ex ) { SqlContext.Pipe.Send(ex.Message); } } } I wanna handle this exception in my catch block. How can I do this?

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

How to tell if TextChanged fired in response to user input

I have a TextBox that triggers a calculation when the user types text.  I am recalculating any time that the user enters a new value by responding to the TextChanged event.  This calculation can be manually overridden. I am finding that the TextChanged event is firing during databinding, as it should, but this is blowing away the manually overridden calculation.  I used to be able to tell if the user had initiated this change by checking the Modified property in a System.Windows.Forms.TextBox. Is there a way to tell if the TextChanged event is in response to user input?  I only want my calculation to take place in response to user input and not in response to databinding or other in code changes to the Text property.

Asynchronous callbacks not getting fired in asp.net web parts

I am trying to create a portal making use of asp.net Web Parts, Web Part Zones and WebPartManager. I have created a custom control as my webpart by inheriting from the standard Web Part. Inside the Web Parts I am making use of controls (3rd party) which allow asynchronous callbacks. However my asynchronous callbacks are not getting trigerred from the web part. I am creating the web part and the web part zones at runtime. The structure of my WebPart is as follows <Button><ClientSideClick = "CallBackMethod" </Button> <GridView></GridView> protected CallBackMethod() { RefreshGrid() } Whenever I click on the button the Page_Init of the aspx page on which the webpart gets executed recreating my webpart and webpartzone and also the CreateChildControls method gets executed but the callback event does not trigger. Has anyone faced something similar?

Trigger can't work well

I find the system don't lanuch "Delete From DBInfo where ProjectID in (select ProjectID from deleted)", what error wih me ? Thanks!   ALTER TRIGGER TriggerDeleteFavoriteProjectAndInfo ON dbo.DBProject FOR INSERT AS  Delete From DBFavoriteProject where ProjectID in (select ProjectID from deleted)  Delete From DBInfo where ProjectID in (select ProjectID from deleted)

Constraint and Trigger

What is the difference between Constraints and Triggers in SQL Server 2005?Explain Briefly?

Exception not raised on instead of trigger

I am checking some data prior to insert using an instead of trigger.  The trigger checks to see if a person being inserted has specific status ( bool(bit) isStaffManager=1) in the Person table.  If bit = 0 an exception should be thrown.  For some reason the trigger seems to try to insert before raising my exception and throws a null exception when @Manager is unassigned.  If I assign a value, even an invalid one, to the variable it works OK.  I don't understand why. From the code below if I remove the line "Set @Manager = ''; (marked near the end of the trigger code) I get the error "can't insert null into manager..." If I put the line in it works OK- Raises my exception with message - "Manager Required...".  Why is the trigger attempting an insert instead of throwing the exception?  And why does the line this assignment fix this? TIA CREATE TABLE [dbo].[Job](  [Job] [varchar](64) NOT NULL,  [Description] [varchar](max) NOT NULL,  [Manager] [varchar](64) NOT NULL,  [ProjectCode] [varchar](6) NOT NULL Default 0,  [ExecptionSourceTitle] [varchar](64) NOT NULL Default '',  CONSTRAINT [PK_Project] PRIMARY KEY CLUSTERED (  [Job] ASC ) ) ON [PRIMARY] CREATE TABLE [dbo].[Person](  [Person] [varchar](64) NOT NULL,  [HireType] [c

Trigger a macro anytime a site or web is opened in SharePoint Designer

Within SharePoint Designer (not interfacing with SharePoint services), I would like to have a macro automatically run anytime a web is opened. I assume I need to use the OnWebOpen VBA event but I'm not sure how to write this into a module. Is there any chance someone could assist me with what this sub routine should look like? Thank you! Greg

The multi-part identifier "deleted.DataEncryptionKeyID" could not be bound : trigger

Hello there, I created a trigger and I am trying to only do stuff if my inserted.column_name1 is different from my deleted.column_name1. Here is my code : alter trigger EncryptionKeyUpdate on [BackupJobDefinition] after update AS if deleted.DataEncryptionKeyID != inserted.DataEncryptionKeyID begin     INSERT BackupJobDefinitionLifeTime         select             'Old',             GETDATE(),             system_user,             JobDefinitionID,             DataEncryptionKeyID,             1        from deleted     INSERT BackupJobDefinitionLifeTime         select             'New',             GETDATE(),             system_user,             JobDefinitionID,             DataEncryptionKeyID,             1    

Button click event is not fired on button click

 Hi am using jquery flexi gride in web page and it is working fine .But now the problem is ocured when i click button to call server side event.I am not able to find out .Please guide me for this My button is as fallows and i am<asp:Button ID="btnExport" runat="server" Text="Export" Style="font-family: 'Times New Roman', Times, serif" OnClick="btnExport_Click" />  

beginRequest is not fired

Hi,Here is my code:<asp:ScriptManager runat="server" ID="sm"> <Services> <asp:ServiceReference Path="~/AjaxWcfService.svc" /> </Services> </asp:ScriptManager> <script type="text/javascript"> function pageLoad() { var pageRequestManager = Sys.WebForms.PageRequestManager.getInstance(); pageRequestManager.add_beginRequest(onBeginRequest); AjaxWcfService.GetMessage(onRequestResult, onRequestError); } function onRequestResult(result) { console.log(result) } function onRequestError(error) { console.log(error); } function onBeginRequest(sender, args) { console.log("begin request"); } </script> The function OnRequestResult is called and it writes to the console a message that was returned by the method GetMessage of the WCF service. But onBeginRequest is not called. What am I doing wrong?
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