August 27, 2003 at 9:01 pm
Hi all,
Here's what I'm trying to achieve. I have a DEVICE_STATE table which is
constantly update to reflect status of a network device. I have an update
trigger which listens to updates on the "DEVICE_STATE" column. Its first
job is to unconditionally call a Logging stored proc, this works. Its next
job is to call another stored proc, but only if the "DEVICE_STATE" column
value changed from a "Down" to an "Up" value. The second step always runs wihtout testing my condition ,
i.e the trigger fires off the second proc "unconditionally". Here is my code..
please help
----------------------------------------------------------------------------
CREATE TRIGGER TRIGGER_UPDATE_DEVICE_STATE ON dbo.DEVICE_STATE
FOR UPDATE
AS
IF UPDATE([DEVICE_STATE])
BEGIN
BEGIN TRAN
DECLARE@DEVICE_ID INT, @DEVICE_SEV_LEVEL VARCHAR(255), @DEVICE_STATE VARCHAR(255),
@UTC DATETIME, @NOTES VARCHAR(255),
@CUSTOMER_EVENT_ID INT
SELECT @DEVICE_ID = DEVICE_ID,
@DEVICE_SEV_LEVEL = DEVICE_SEV_LEVEL,
@DEVICE_STATE = DEVICE_STATE,
@UTC = UTC,
@NOTES = NOTES
FROMINSERTED
--log the device state change
EXEC sp_Admin_DATA_ALERT_LOGS_FINAL @DEVICE_ID, @DEVICE_STATE, @UTC,
@DEVICE_SEV_LEVEL, NULL, @NOTES, NULL
IF (SELECT ltrim(DEVICE_STATE) FROM DELETED) = 'Node Down'
BEGIN
PRINT 'DEVICE STATE went to UP from DOWN'
--Call the proc which does buss rules for sev level calcs
EXEC sp_Admin_Interface_Sev_Level_Calculator_ @DEVICE_ID
END
COMMIT TRAN
END
August 28, 2003 at 12:13 am
Hi Uday,
quote:
Here's what I'm trying to achieve. I have a DEVICE_STATE table which isconstantly update to reflect status of a network device. I have an update
trigger which listens to updates on the "DEVICE_STATE" column. Its first
job is to unconditionally call a Logging stored proc, this works. Its next
job is to call another stored proc, but only if the "DEVICE_STATE" column
value changed from a "Down" to an "Up" value. The second step always runs wihtout testing my condition ,
i.e the trigger fires off the second proc "unconditionally". Here is my code..
please help
this one should help you
http://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=14461
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
August 28, 2003 at 1:03 am
Hi Frank,
Thanks, I did check out the examples your pointed me to. But, can you tell me, just in pricnciple, whether what I've done the way I've done it [without using bitmask] is flawed fundamentally. If yes, should I use the columns_updated() feature?
Regards
Uday
August 28, 2003 at 1:32 am
Uday, first of all, your second part does not take a set based update into account.
This might give you some problems if more than one device is updated in one statement.
For the rest, you should not use the syntax you're using. Try the following for the if statement :
IF EXISTS (SELECT DEVICE_STATE FROM DELETED where DEVICE_STATE = 'Node Down')
BEGIN ...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply