SQL Server Trigger That Fires Only On Update Of Certain Field?
Aug 14, 2006
Dear Experts,
I'm an Oracle guy, who is being given more SQL Server assignments
lately.
I've been looking for things on the web about this, but I can't
anything so far.
In Oracle, I you can create a trigger on a table that -only- fires if
certain fields are updated.
create or replace trigger trg_some_trigger
BEFORE insert
OF some_field1, some_field2
on tbl_some_table
for each row
....
Is this also possible in SQL Server?
What is the syntax please?
Thanks a lot!
View 2 Replies
ADVERTISEMENT
May 3, 2006
Hi
In SQL Server 2005, does TRIGGER for DML fire per statement or per Record ?
If both, can we define the way it should fires.
thanks / ramanuj
View 11 Replies
View Related
Jan 19, 2000
If I update a record such as
Update MyTable SET MyField = 'MyValue'
where MyKey = 'Key1'
The trigger fires .....
If I update multiple records
Update MyTable SET MyField = 'MyValue'
where MyKey IN ('Key1','Key2')
Does the trigger (Update) on MyTable fire more than once or once.
View 4 Replies
View Related
Apr 12, 2007
hi,
I want a trigger in db aaa to fire when table a_aaa is updated in server a and table b_bbb in db bbb in server b to be populated with data. I know how to write a trigger if fired and the result stays in one server with one db. But I don't know how to do it if between two servers and two db.
a
win server 2003 standard Edition
sql Server 2000
db:aaa
table: a_aaa
column:a_aaa_a
b
win server 2003 standard Edition
sql Server 2000
db:bbb
table:b_bbb
column: b_bbb_b
It's not working because the symtax is incorrect and because I am not sure how to do it between two servers. If it is not correct, where am I wrong? Where should each line be located?? et cetera......
Can anyone help?
Thanks in advance.
CREATE TRIGGER [enddate_changed_on_alert] ON [dbo].[USER_DATA]
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @EndDate as DATE
DECLARE @CompCode as VARCHAR(15)
DECLARE @PhoneGMSM as VARCHAR(10)
DECLARE @PhoneALERT as VARCHAR(10)
SELECT @PhoneALERT=phone1 from AUSSMEDEVIQ1.QDB_Test.USER_DATA;
SELECT @PhoneGMSM=PHONE1 from AUSADFORMULA02.QDB_KS.dbo.USER_DATA_DEVIQ;
UPDATE AUSADFORMULA02.QDB_KS.dbo.USER_DATA_DEVIQ SET EXT4 = "111"
WHERE AUSSMEDEVIQ1.QDB_Test.USER_DATA.@Phone = AUSADFORMULA02.QDB_KS.dbo.USER_DATA_DEVIQ;
WHERE:
USER_DATA_DEVIQ = b_bbb_b
AUSADFORMULA02 = bbb
QDB_KS = b_bbb
USER_DATA = a_aaa_a
AUSSMEDEVIQ1 = aaa
QDB_Test = a_aaa
END
GO
View 9 Replies
View Related
Nov 21, 2007
I have an update trigger that works wonderful as long as I am updating the row in Enterprise Manager, but if I update the same column and row using an update statement in Query Analyzer the trigger doesn't fire.
Any ideas why?
TIA.
View 13 Replies
View Related
Nov 11, 2013
I want to update a field with a trigger only if a specific field is updated.
When I try the code below, it updates the field when any field in the record is updated. Is there a way to only make look at picked_dt?
ALTER TRIGGER [dbo].[UpdatePickedDate]
on [dbo].[oeordlin_sql]
after update
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
[Code] .....
View 4 Replies
View Related
Feb 16, 2005
Need to parsing serverName and databaseName to run a dynamic query to get serverName and databaseName and employee_ID via a accountID parameter.
-----------------------------
declare @stringSQL varchar(200)
select @stringSQL=
'insert into temp1 select '+@AccountID+' accountID, employee_ID from ' + @serverName +'.dbo.'+@databaseName+'.tblEmployee where inactive=0'
print @stringSQL_GetUserName
exec (@stringSQL_GetUserName)
select * from temp1
------------------------------
above dynamic query works fine.
Howevery, this should be run only under insertion event. When I put it in a proc to run within the insertion trigger or put the whole sql statement within the trigger:
1. when ran at a MSDE server
MSDTC on server is unavailable.
2. when ran at a SQL2000 developer testing server with the distributed transaction coordinator on, the insertion a record in the isql/w hang there. Could not even to kill this query, and have to stop and restart the SQL server.
Then I just want to return the dynamic query result without 'insert into temp1 ', the result is still hang...
Is there a way to let the insert trigger to run a dyanamic query which linked to around 10 servers?
thanks
David
View 4 Replies
View Related
Aug 2, 2007
Hi -
I know my way around VS but I am just exploring "advanced" SQL Server 2005 and have run into a challenge which I think a trigger could solve, but I am not sure and also don't know how to set that up. So any help or links to tutorials are highly appreciated.
Here is the challenge: I have a table with a number of fields, among them RequestID (bigint) and Booktime (datetime). What I would like to happen is whenever someone writes a value different from NULL into RequestID, Booktime gets set to the current timestamp. When RequestID gets set to NULL, Booktime gets set to NULL.
Is there a way to do this with a trigger (or an otherwise elegant way)?
Thanks in advance for ANY help or ideas.
Oliver
View 3 Replies
View Related
Feb 21, 2013
I'm tracking sales reps and the companies they represent. I have 2 tables. A header table that has the SalesRep (a key field) the name, phone, address, etc.A line file that will have the sales rep multiple times, each line having a vendor they represent.
SalesRepHeader table:
SalesRep name phone VendorList
Bob Bob_Smith 111-222-3333
John John_Young 123-456-56789
Mary Mary_Kerns 567-876-98765
[code]...
Whenever the SalesRepLine file is added to or updated I want the header file field VendorList to be updated with a concat of the vendors for this sales rep so the SalesRepHeader file would look like this.
SalesRep name phone VendorList
Bob Bob_Smith 111-222-3333 Samsung, Kenwood, JVC
John John_Young 123-456-56789 Apple, HP
I need the update to be automatic.
View 12 Replies
View Related
Aug 28, 2007
I want to create a trigger that, when a field is updated or a record is inserted, counts the number of words in "field1", inserting this numeric value into another field in the same table. What would be the most efficient, fastest method for doing this?
View 15 Replies
View Related
Apr 16, 2015
If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?
EXAMPLE:
CREATE TABLE dbo.MYTABLE(
ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,
[Code] ....
If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
VALUES('John','Smith',NULL)
INSERT INTO dbo.MYTABLE( FirstName, LastName)
VALUES('John','Smith')
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
SELECT FirstName, LastName, NULL
FROM MYOTHERTABLE
View 9 Replies
View Related
Sep 18, 2001
Hi Iam trying to do a trigger that everytime I Update a record de date get update too I finally find a trigger close to that, but this trigger update all the dates from all the record of the same table I wonder is there are a way that I can do it by the date of the record, if somebody could help I will really appreciate.
Thi is the trigger that I have so far
Create Trigger Update_Date
on DBO.Company After Update as
Update dbo.Company
Set ActualiizationDate=Getdate()
go
View 1 Replies
View Related
Jul 23, 2005
Hi for all on this...I'm using MS SQL Server 7.0 SP4 in some customers to store some datafrom an aplication developed by me.I created an trigger to run on update.When I run an update command on it's table and the where conditionreturns only one row, the trigger is executed ok, but if wherecondition returns more than one rows, the trigger don't run.I don't know what happens there...If any can help on this, I will be thankfull!Regards!Lucio
View 7 Replies
View Related
Sep 13, 2007
I have 2 database servers ( 2 hardware-servers : A and B ) and I ve written a trigger for update in server A to execute insert statement in server B through linked servers, is there other way to achieve this without linked servers?? All using T-SQL.
Best Regards
Joseph
View 4 Replies
View Related
Nov 28, 2007
Hi all,
I have a trigger for column eISBNEnteredDate on update or insert changes of eISBN of the table Products2 ( both belong to the same table). The column eISBNEnteredDate can either be added manually along with eISBN value or when only eISBN value is entered it is updated with present date.
The problem I am facing is when I send eISBN along with eISBNEnteredDate the present date is what is getting saved. Upon the same record when a new date is updated the new date is getting saved. Can someone tell me where I am going wrong?
Here is my trigger:
Code Block
ALTER TRIGGER [dbo].[Products2_eISBNEnteredDate] ON [dbo].[Products2]
For Insert, Update
As
Begin
Declare @ProductId int
Declare @eISBN Varchar(17)
Set @eISBN = '0'
If ( Update(eISBN) )
Begin
Select @Productid = I.Productid,@eISBN = I.eISBN
From Inserted I Left Join Deleted d on I.Productid = D.Productid
Left join Products2 P on P.Productid = I.Productid
Where (ISNULL(I.eISBN,'') <> ISNULL(D.eISBN,''))
If (IsNull(@eISBN,'') <> '' and IsNull(@eISBN,'') <> '0')
Begin
Update Products2
Set eISBNEnteredDate = getdate()
Where ProductID in (select i.ProductID
From Inserted i
Left join Deleted d on d.ProductID = i.ProductID
Where (i.eISBN is not null or replace(i.eISBN,' ','') != '') --where the new eISBN is not null or blank
and (d.eISBN is null or replace(d.eISBN,' ','') = '') --where the old eISBN is null or blank
and isnull(i.eISBN,'') != isnull(d.eISBN,'') --where the new eISBN is not equal to the old ISBN13
and d.eISBNEnteredDate is null)
End
If IsNull(@eISBN,'') = '' and IsNull(@eISBN,'') = ''
Begin
Update Products2
set eISBNEnteredDate = NULL
Where ProductID = @Productid
End
End
End
View 5 Replies
View Related
Jul 20, 2005
Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'
View 1 Replies
View Related
Jan 30, 2015
We have an UPDATE trigger that is failing. This seems like a basic task - we want to write a record to a separate tracking table when our main transaction table is updated for any reason. Our assumption is that we have a reference to the data from the "inserted" record that was just updated. The scenario here is that we are running a batch process which READS several thousand records in our transaction table each evening.
We then mark each individual record as processed on the transaction table and expect that the UPDATE trigger will successfully fire (it is not). The version of our trigger listed below shows our attempt to deal with the fact that TransactionID does NOT exist from "inserted." We also have a version of this trigger that deals with INSERTS - it works flawlessly.
ON [dbo].[FPS_Transaction]
AFTER UPDATE
AS declare @trxId uniqueidentifier;
BEGIN TRY
SET NOCOUNT ON
[code]...
View 6 Replies
View Related
Jul 20, 2005
Hi there,I'm pretty new to SQL and am having some porblems with a linked server.I have a table on a SQL server which stores employee information.I also have a view on a linked server which stores the same information.What I would like to happen is, whenever the view changes on the linkedserver I want the information to be changed in the table on my server.I've been trying to write a trigger to do this, but have had noluck so far.Can anyone help me?ThanksSimon--Posted via http://dbforums.com
View 1 Replies
View Related
Jul 21, 2015
We have a database on a 2005 box, which we need to keep in sync with one on a 2014 box (until we can turn off the one on 2005). The 2005 database is still being updated with changes that must be applied to the 2014 database, given the nature of the data (medical documents) we need to ensure updates are applied to the 2014 database in very near real time (these changes are - for example - statuses, not the documents themselves).
Cunning plan #1, ulgy - not at all a fan of triggers - but use an after update trigger to run a sp on the remote box via a linked server in this format, with a SQL Server login for the linked server with permissions to EXEC the remote proc.
CREATE TRIGGER [dbo].[SourceUpdate] ON [dbo].[SourceTable]
AFTER UPDATE
AS
SET XACT_ABORT ON;
SET NOCOUNT ON;
IF UPDATE(ColumnName)
[Code] ....
However, while the sp can be run against the linked server as a standalone query OK, when running it in a trigger it's throwing
OLE DB provider "SQLNCLI" for linked server "WIBBLE" returned message "The transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Procedure TheAfterUpdateTrigger, Line 19
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "WIBBLE" was unable to begin a distributed transaction.
Whether it actually possible to call a proc on a remote box via a trigger and if so what additional hoops need to be jumped through (like I said, it'll run OK called via SSMS)?
View 3 Replies
View Related
Jun 18, 2015
I am writing a trigger for getting values to auditlog table when the values gets updated. Below is the code of my trigger.
CREATE TRIGGER [dbo].[Update_Temp] ON [dbo].[Temptable1] FOR UPDATE
AS
DECLARE @bit INT ,
@field INT ,
@maxfield INT ,
@char INT ,
@fieldname VARCHAR(128) ,
@TableName VARCHAR(128) ,
[Code] ....
The code is working fine when the table has primary key associated. However due to some restrictions I will not be able to have a primary key for some tables. I want to implement the same trigger in those tables too. When there is primary key, that primary key needs to get inserted into the audit table and if there is no primary key, i want a specific column value to get inserted instead of the primary key value into the audit table.
For example, i have a student table in which there is a student id, name, dob. there is no primary key defined for the table. So when i update the name or dob, i need the student id to get inserted into the Pk column of the audit table.
I tried modifying the code by checking the @pkcols for Null and if its null to get the old value as the primary key however I was not able to do it .
View 1 Replies
View Related
Jan 3, 2005
hi!
I have a big problem. If anyone can help.
I want to retrieve the last update time of database. Whenever any update or delete or insert happend to my database i want to store and retrieve that time.
I know one way is that i have to make a table that will store the datetime field and system trigger / trigger that can update this field record whenever any update insert or deletion occur in database.
But i don't know exactly how to do the coding for this?
Is there any other way to do this?
can DBCC help to retrieve this info?
Please advise me how to do this.
Thanks in advance.
Vaibhav
View 10 Replies
View Related
Jul 8, 2015
I have a table where table row gets updated multiple times(each column will be filled) based on telephone call in data.
Initially, I have implemented after insert trigger on ROW level thinking that the whole row is inserted into table will all column values at a time. But the issue is all columns are values are not filled at once, but observed that while telephone call in data, there are multiple updates to the row (i.e multiple updates in the sense - column data in row is updated step by step),
I thought to implement after update trigger , but when it comes to the performance will be decreased for each and every hit while row update.
I need to implement after update trigger that should be fired on column level instead of Row level to improve the performance?
View 7 Replies
View Related
Apr 21, 2015
I have a situation where I have Table A, Table B.
View C is created by joining table A and table B.
I have written a instead of trigger D on view C.
I do not insert/update/delete on the view directly.
For every insert/update in table A /B the values should get insert/update in the view respectively. This insert/update on view should invoke the trigger.
And I am unable to see this trigger work on the view if any insert/update occurs on base table level.
Trigger is working only if any operation is done directly on the view.
View 2 Replies
View Related
Mar 24, 2015
I have a table with 201 columns . I am importing 200 columns from a file using DFT. I want update the 201th column with the fileId of the file that just imported. I am storing the fileId of the file in varFileID .
How do I go back and update the 201th column ( column name sfileId) with the varFileID value?
I can use Execute SQL Task but how will I know it's the records of the files that I just imported not other rows.
View 0 Replies
View Related
May 30, 2008
Hi,
I am not sure if this is the right forum to post this question.
I run an update statement like "Update mytable set status='S' " on the SQL 2005 management Studio.
When I run "select * from mytable" for a few seconds all status = "S". After a few seconds all status turn to "H".
This is a behaviour when you have an update trigger for the table. But I don't see any triggers under this table.
What else would cause the database automatically change my update?
Could there be any other place I should look for an update trigger on this table?
Thanks,
View 3 Replies
View Related
Feb 15, 2008
Hello
I've to write an trigger for the following action
When a entry is done in the table Adoscat79 having in the index field Statut_tiers the valeur 1 and a date in data_cloture for a customer xyz
all the entries in the same table where the no_tiers is the same as the one entered (many entriers) should have those both field updated
statut_tiers to 1
and date_cloture to the same date as entered
the same action has to be done when an update is done and the valeur is set to 1 for the statut_tiers and a date entered in the field date_clture
thank you for your help
I've never done a trigger before
View 14 Replies
View Related
Mar 30, 2004
Hi all,
I have a problem about a query to update a table
UPDATE Email SET EmailDT='31 Mar 2004' WHERE Idx={BDF51DBD-9E4F-4990-A751-5B25D071E288}
where Idx field is a uniqueidentifier type and EmailDT is datetime type. I found that when this query calling by a VB app. then it have error "[Microsoft][ODBC SQL Server Driver]Syntax error or access violation" and i have tried again in Query Analyzer, same error also occur, the MS SQL server is version 7. Please help. thanks.
View 2 Replies
View Related
May 12, 2006
Not a SQL guy but can do enough to be dangerous :)Trying to update a record. We have records that have a field with datasurrounded by some comment text such as *** Previous Public Solution*** Start and *** Previous Public Solution *** End . What I am tryingto do is write a SQL statement that will:Check that field C100 = TICKET0001 (to test with one record beforerunning on whole db)Check that field C101 is = ClosedCheck that field C102 is nullCopy field C103 data to field C102 and strip out any words such as ***Previous Public Solution *** Start and *** Previous Public Solution*** endThanks for any help!Kevin
View 1 Replies
View Related
Jun 30, 2006
In Pablo Castro webcast, First Look at ADO.NET 2.0, he mentions the use of UpdateBatchSize which I think would be handy.However, I was not able to get it to work.
Dim t As New Global.System.Data.SqlClient.SqlDataAdapter("Select * from Table_1", Global.System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString1").ConnectionString)Dim d As New Global.System.Data.DataTablet.FillSchema(d, SchemaType.Source)Dim cb As New Global.System.Data.SqlClient.SqlCommandBuilder(t)For c As Int32 = 1 To 10000Dim r As Global.System.Data.DataRow = d.NewRowr("id") = Global.System.Guid.NewGuidr("val") = CType(Rnd(), Int32)d.Rows.Add(r)Nextt.UpdateBatchSize = 100t.Update(d)d.Dispose()t.Dispose()
What ends up showing in Sql Server Profiler (Sql Server 2005) is each Insert being executed in it's own statement:exec sp_executesql N'INSERT INTO [Table_1] ([id], [val]) VALUES (@p1, @p2)',N'@p1 uniqueidentifier,@p2 int',@p1='3793CB5E-3B7A-45E7-9A53-0BD528BB6B07',@p2=1
I think I made this example very simple and yet can't fathom why it won't batch the statements.
I'm somewhat aware of SqlBulkCopy and was very pleased with that speed, but don't think it would handle the Update,Delete,Insert that the SqlDataAdapter.Update would.
Originally started using xsd DataSets until I saw the data tutorials in the Learning section here in which case I copied out the autogenerated TableAdapter classes and fuddling with them to do what I want since batching was not something I saw in TableAdapters.
Does anyone see what I'm missing here?Nathan
View 2 Replies
View Related
Mar 17, 2008
I have "sqlGetReservationMembers" datasource in my ascx code and another datasource in 'KillReservation'. As you can see "sqlGetReservationMembers" uses a SELECT command only. I have verified this several times. The 3rd block below is the only place in code-behind where "sqlGetReservationMembers" is referenced. When the "KillReservation" fires I get an error stating "Deleting is not supported by data source 'sqlGetReservationMembers' unless DeleteCommand is specified". I'm confused because I can't see where the 'sqlGetReservationMembers' delete event is being fired. I have removed the datasource and recreated it but the problems returns. What am I missing?
Thanks
<asp:SqlDataSource ID="sqlGetReservationMembers" runat="server" ConnectionString="<%$ ConnectionStrings:SiteSqlServer %>" SelectCommand="sp_GetReservations" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:ControlParameter ControlID="gvMyRides" Name="ID" PropertyName="SelectedValue" Type="Int32" /> </SelectParameters> </asp:SqlDataSource>--------------------------------------------------------------------------------- Protected Sub KillReservation(ByVal RideID As Integer, ByVal MemberID As Integer)
Using Myconnection As New SqlConnection(Config.GetConnectionString("SiteSqlServer")) Dim Mycommand As New SqlCommand("sp_KillReservation", Myconnection) Mycommand.CommandType = CommandType.StoredProcedure Mycommand.Connection.Open() Mycommand.Parameters.Add(New Parameter("@MemberID", TypeCode.Int32, MemberID)) Mycommand.Parameters.Add(New Parameter("@RideID", TypeCode.Int32, RideID)) Mycommand.ExecuteNonQuery() Myconnection.Close() End Using
End Sub----------------------------------------------------------------------------------Protected Sub gvMyRides_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles gvMyRides.SelectedIndexChanged
sqlGetReservationMembers.SelectParameters.Item("ID").DefaultValue = Me.gvMyRides.SelectedValue Me.gvReservationList.DataSourceID = Me.sqlGetReservationMembers.ID
..More Code..........
End Sub-----------------------------------------------------------------------------------
View 3 Replies
View Related
Mar 27, 2008
I am using the SqlDependency to notify me of data changes in a table. However, as soon as I start it, the OnChange event fires over and over even though no data has changed.
Essentially All I want to do is be notified when records are inserted into a table. I am able to get another example to work using a Service Broker Queue and a sql query of WAITFOR ( RECEIVE CONVERT(int, message_body) AS msg FROM QueueMailReceiveQueue ) However I would prefer not to use a queue for once my messages have been read they are taken off the queue and I would rather control that manually.
Any help with getting SqlDependency to notify my app when records are added and not over and over when the data has changed would be great.
Here is my code:
Code Snippet
public partial class Form1 : Form {
public static event OnChangeEventHandler OnChange;
string _strConnString = "Data Source=localhost;Integrated Security=SSPI;Initial Catalog=email_queue;Pooling=False;";
string _strSql = "SELECT email_id from email where isprocessed = 0";
private DataSet dataToWatch = null;
private SqlConnection connection = null;
private SqlCommand command = null;
SqlDependency dependency = null;
SqlDataReader sdr = null;
public Form1() {
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e) {
SqlDependency.Stop(_strConnString);
SqlDependency.Start(_strConnString);
if (connection == null) {
connection = new SqlConnection(_strConnString);
connection.Open();
}
if (command == null) {
command = new SqlCommand(_strSql, connection);
}
if (dataToWatch == null) {
dataToWatch = new DataSet();
}
GetData();
}
private void GetData() {
dataToWatch.Clear();
command.Notification = null;
dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
command.CommandTimeout = 400;
using (SqlDataAdapter adapter = new SqlDataAdapter(command)) {
adapter.Fill(dataToWatch, "email");
}
}
private void dependency_OnChange(object sender, SqlNotificationEventArgs e) {
ISynchronizeInvoke i = (ISynchronizeInvoke)this;
if (i.InvokeRequired) {
OnChangeEventHandler tempDelegate = new OnChangeEventHandler(dependency_OnChange);
object[] args = { sender, e };
i.BeginInvoke(tempDelegate, args);
return;
}
dependency = (SqlDependency)sender;
dependency.OnChange -= dependency_OnChange;
this.Text = DateTime.Now.ToString();
GetData();
}
private void Form1_FormClosed(object sender, FormClosedEventArgs e) {
SqlDependency.Stop(_strConnString);
if (connection != null) {
connection.Close();
}
}
}
View 4 Replies
View Related
Dec 17, 2001
I have an update trigger which fires from a transactiion table to update a parent record in another table. I am getting no errors, but also no update. Any help appreciated (see script below)
create trigger tr_cmsUpdt_meds on dbo.medisp for UPDATE as
if update(pstat)
begin
update med
set REC_FLAG = 2
from deleted dt
where med.uniq_id = dt.uniq_id
and dt.pstat = 2
and dt.spec_flag = 'kop'
end
View 1 Replies
View Related
May 30, 2008
I am trying to update a fields with an UPDATE statement but I keep getting the error message when I run the query.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I have this Update trigger that I know is causing the error message because I guess it's not built to manage multi-row updates.
Can someone help me re-write it. I also tried using the WHERE p.ID = p.ID but when I do that it modifies all rows in the modifieddate column instead of just the cells/rows that I'm updating
ALTER TRIGGER [dbo].[MultitrigCA]
ON [dbo].[ProdDesc]
AFTER UPDATE
AS
SET NOCOUNT ON
IF UPDATE (codeabbreviation)
UPDATE p
sET p.ModifiedDate = GETDATE()
FROM ProdDesc AS p
WHERE p.ID = (SELECT ID FROM inserted)
View 7 Replies
View Related
