.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

Help with Update Trigger

Posted By:      Posted Date: October 14, 2010    Points: 0   Category :Sql Server

Hi There,

I am trying to create a trigger that will fire off an e-mail to a user when a field is updated in one of my tables.

My table structure is currently:


When an admin cancel's an open ticket, it set's the ticket_status to '5' - so I am trying to write a trigger that basically says, when ticket_status = 5 - exec msdb.dbo.sp_send_mail, with the params that I select. I have tried the following:

create trigger [schema].[sendemails] on [schema].[ticketstatustable]
after UPDATE

declare @ticketid = numeric(18,0);
declare @ticketstatus = numeric(1,0);

select @ticketid = ticket_id,@ticketstatus = ticket_status from updated

if @ticketstatus = '5' BEGIN

........more code here that grabs email address from database., et

View Complete Post

More Related Resource Links

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

Update panel with async trigger not working


 I have following code and when I select from my drop down list the progress controls do not show and my grdiview is not being filled either. If I leave out the update panel from my code things are working fine.

  <asp:UpdatePanel ID="UpdatePanel2" UpdateMode="Conditional"  runat="server">
                 <asp:AsyncPostBackTrigger ControlID = "TabContainer1$TabPanel1$DropDownList3" EventName = "SelectedIndexChanged" />
                 <asp:Label ID="lblText" runat="server" Text="Loading data ......"></asp:Label>
                 <asp:Image ID="img1" runat="server" src="Icons/ajax-Loader.gif" /> 
<cc1:TabContainer ID="TabContainer1" runat="server" Height="20px" ActiveTabIndex="0">
                    <%--OnActiveTabChanged="TabContainer1_ActiveTabChanged" AutoPostBack="true">-

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.




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?

Update Trigger For Multiple Rows



I've written a trigger that fires on a table after an insert and SHOULD update the destination table if a record exists or performs an insert if the record doesn't exist. I'm having trouble because in some instances it won't run because the subquery is returning multiple rows from the INSERTED table. Code is below. Any help is greatly appreciated.


CREATE TRIGGER [dbo].[InsertOverflowHistory_Trigger]
  ON [dbo].[ContainerDetail]

  DECLARE @Store       NCHAR(5);
  DECLARE @ItemWeight    DECIMAL(12,4);
  DECLARE @ItemCube     D

trigger update



I have two table:

Parent Table1:

Id, FirstName

Child Table2

Id, FirstName

In the Parent table FirstName data:

The child table FirstName data:

First table, column FirstName, data = Joe.

How to change data in seconed table, data = Joe.

Instead, Joe, I want Joey to another table.

How to change data in first table, column FirstName, data = Joe,

trigger and cascade update


If I use trigger, I do not need the table to determine cascade update?

how to use DML update trigger to track all table changes


Hi All,

Somebody is making updates to a table that shouldn't be updated!  I've been asked to track which user, the updated column name(s), and the before and after value(s).  It doesn't matter how many or which field(s) had the value updated.  If it was updated, we need to track it.

The straightforward method seems to be using a DML update trigger like the example shown at http://msdn.microsoft.com/en-us/library/ms187326.aspx, but there's over 30 columns in the table that need to be watched.  From the example at that page, it appears I would need a "IF UPDATE(my_col_name)" statement for each and every column we'd like to watch.  Correct?

How would we ID the user that made the change?  I assume there's a variable available within an update trigger that holds that value.

My main question ends there, but here's a little more background that may affect any advice:

1.  We can't track changes in a table.  Instead, they have to be written to the SQL Log, which I've found can be accomplished using "xp_logevent".
2.  A separate log entry for every field updated in a row is not a requirement.  One log entry with a row showing the old values and another row showing the new values would suff

here is trigger if exist then update else inser but not working properly for bulk update(10 or 15 ro



i have a trigger as follows



TRIGGER [ table_detail_insert_update]








update trigger based on value in another table


I would like to create a trigger to stop the update of an existing record, if the date is less than a date for that event type in another table

if the date of the record being updated is < select Date from OtherTable where eventType = (event type of row trying to be updated)

I would still need to do inserts but not updates.

This is my 1st trigger so excuse me if its a really simple thing, but i have googled and dont find anything quite like i think i need

After Update trigger for a record which does not exist in table.


Hi Everyone,

I have a update query which sometimes results in no row updated (i.e. 0 rows updated),

I want to apply after update trigger on it , if update results in 0 row affected then insert that record,

I have written following trigger for it,

ALTER TRIGGER [dbo].[table1]

   ON  [dbo].[table1]





            SET NOCOUNT ON;

ASP.Net Gridview Edit Update Cancel Commands

In ASP.Net 2.0, GridView Control also provides the functionality to edit and update the data retrieved from the database using CommandField template. You can cancel the action using Cancel Command of the CommandField. GridView consists of events that can be used to perform the actions like edit, update and cancel upon the Data items displayed in the ASP.Net GridView Data Control.

How to format and update GridView and DataGrid rows using JQuery

The behavior described in this question is as expected. When you set text of a cell in grid, it directly affects HTML that is going to be rendered. When you set text value of a cell, it means that you are setting innerText of the cell. The column that GridView creates for command fields (Edit, Delete and Select) are a (anchor) or button elements. So you can see what will happen if you set text value in that cell. It will wipe out those link or button controls and replace them with simple text string.

Update Vs SystemUpdate

Many of you might noticed that share point ListItem has Update() method as well as SystemUpdate().

What is the difference between these two methods and why MOSS has two different APIs for updating an ListItem

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.

>>>How to update multiple rows of gidview by one click



I want to add multiple rows of a gridview if the data for multiple fields are same.

Idea for this is that a user may search a record with some parameter e.g. Sector, plot.

For example 15 records being displayed in gridview.

There should be checkbox before every row and if user wants to update Plot and Flat No that is same for 10 rows out of 15 so he will check the rows and update.

For this a dialog box should appear asking for the parameter i.e. Plot and Flat or something else next time may be it will be City or country. Moreover, there is already an Edit button to update records, when user click it another page open for the record to be updated and whatever he wants to update, it updates.

Hope everone understand the scenario.

I don't want to disturb the already provided Edit functionality and want to provide same record to be updated.

Any idea in this regard would be appreciated.

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