If UPDATE test for Triggers
-
I have a client that want's to use a trigger to notify them when certain events occur! ie if the Shipcity in the Northwind Orders table is updated and may be if this occurs only for orders created by a specified employee!
So I've been tasked with writing an app that generates this trigger, I have it working apart from being able to determine which field was updated! I've tried using the "if Update(columnname)" syntax
i.e. if update(Shipcity)
That is accepted syntatically by SQL 2000 BUT doesn't allow anything to be added after it
ie. if update(Shipcity) is true
I don't want to use the bitwise column identifier as I can't determine which column may be selected. and I must be able to identify the trigger column in the inserted table, one of the programme requirements!
Can anyone help me with the syntax here or some suggestions?
Marke
-
Can you post the code you have so far?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
The syntax is strictly
IF UPDATE(colname)
You can check for multiple columns like so.
IF UPDATE(colname1) AND/OR UPDATE(colname2)
Or that that column was not in the update like so
IF NOT UPDATE(colname)
But those are the only legal syntaxes.
-
Haha, now I understand
You want the same syntax like VB
If something = True...
The addendum = True isn't necessary at all. *Might* make it more readable, but that's all.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Below is an example of the trigger I've created! It works fine BUT returns data when any field in that row is modified! In this case I need to check if ShipCity is changed and if AND only if it is changed process the rest of the trigger, AND That's what I can't do!!
Any suggestions?
Marke
CREATE TRIGGER LODE_515341 ON dbo.Orders
FOR INSERT, UPDATE
AS
DECLARE @TriggerVal int
DECLARE @ProductName nvarchar(40)
BEGIN
if update(ShipCity)
begin
SET @TriggerVal = (Select OrderID FROM inserted WHERE OrderID = 10250 )
if @TriggerVal is null goto endpoint
Declare @Text Varchar(4000)
SET @ProductName = ( Select Top 1 ProductName FROM Inserted, [Order Details], Products WHERE inserted.Orderid = [Order Details].Orderid and [Order Details].Productid = Products.Productid )
BEGIN
Set @Text = ' ProductName = ' + @ProductName
Execute master..xp_Alerting '127.0.0.1' , 50001, @Text
End
endpoint:
End
end
-
The if update() works perfectly for me. Are you sure @TriggerVal is correctly assigned a value.
This http://www.sqlservercentral.com/columnists/jtravis/understandingthedifferencebetweenisnull.asp might be interesting for you
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
The if update() checks only if a column is updated and does not check whether data really changed.
To check for data changes I use statements like this:
Declare @bIsUpd_OrderID bit
IF Exists (Select * From INSERTED i LEFT JOIN DELETED d ON i.OrderID = d.OrderID WHERE (isNULL(i.OrderID, '') <> isNULL(d.OrderID, '') )) Set @bIsUpd_OrderID = 1 Else Set @bIsUpd_OrderID = 0
--...
If @bisUpd_OrderID = 1 Begin
--- do some work here if OrderID changed ...
End
-
Thanks to everyone for the help. I've got it sorted now and learn't some useful info on the way.
Marke
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply