[solved] Update Trigger - If/Else statment

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

[solved] Update Trigger - If/Else statment

Author

Topic

mallorz4
Starting Member

27 Posts

Posted - 2014-02-15 : 19:33:38

I'm trying to do something like this and have tried a few Google searches but I am not exactly sure what to search for ...

CREATE TRIGGER TableX_UPDATE
ON TableX
AFTER UPDATE
AS
UPDATE ColX
IF (ColX BETWEEN 1 AND 2) THEN SET ColX = ColX * 100
ELSE SET ColX

...make sense? Basically, check the update value of said column and multiply it by 100 if between two values.

Any push in the right direction would be appreciated.

mallorz4
Starting Member

27 Posts

Posted - 2014-02-15 : 20:16:56

I managed to come up with this, which succeeds with no errors ..



IF OBJECT_ID('Products_UPDATE') IS NOT NULL
	DROP TRIGGER Products_UPDATE;

GO

CREATE TRIGGER Products_UPDATE
	ON Products
	AFTER UPDATE
AS
	Declare @DiscountPercent money
	SELECT DiscountPercent = @DiscountPercent 
	FROM Products
	IF (@DiscountPercent > 100 OR @DiscountPercent < 0)
		THROW 50001, 'DiscountPercent out of range.', 1;
		ROLLBACK TRAN;
	IF (@DiscountPercent BETWEEN 0 AND 1)
	BEGIN
		UPDATE Products SET DiscountPercent = @DiscountPercent * 100
	END;

When I try to test it with the following, two things happen depending on how you go about it. If you select and run the whole thing, it returns 11 rows with a 'DiscountPercent' value of NULL. If you select only the UPDATE statement (3 lines) it says 11 rows have been affected although no changes take place.

BEGIN TRY
	UPDATE Products
	SET DiscountPercent = .3
	WHERE ProductID = 4;
	Print 'Update successful.'
END TRY
BEGIN CATCH
	PRINT 'An error occured. Update was not successful.';
	PRINT 'Error number: ' + CONVERT(varchar, ERROR_NUMBER());
	PRINT 'Error message: ' + CONVERT(varchar, ERROR_MESSAGE());
END CATCH;

Is this close? Why does it do that when I specify a WHERE clause?

Thank you.

mallorz4
Starting Member

27 Posts

Posted - 2014-02-15 : 21:48:04

FYI, got it. Have some debugging to do with test statements and the like but this seems to be working:

IF OBJECT_ID('Products_UPDATE') IS NOT NULL
	DROP TRIGGER Products_UPDATE;

GO

CREATE TRIGGER Products_UPDATE
	ON Products
	AFTER UPDATE
AS
DECLARE @DiscountPercent money, @ProductID	int
	BEGIN
		SELECT @DiscountPercent = DiscountPercent, @ProductID = ProductID
		FROM Inserted;
			IF (@DiscountPercent > 100 OR @DiscountPercent < 0)
				THROW 50001, 'DiscountPercent out of range.', 1;
				ROLLBACK TRAN;
			IF (@DiscountPercent BETWEEN 0 AND 1)
				UPDATE Products SET DiscountPercent = @DiscountPercent * 100 WHERE ProductID = @ProductID;
	END

mallorz4
Starting Member

27 Posts

Posted - 2014-02-15 : 22:08:02

quote:

Originally posted by mallorz4

FYI, got it. Have some debugging to do with test statements and the like but this seems to be working:

IF OBJECT_ID('Products_UPDATE') IS NOT NULL
	DROP TRIGGER Products_UPDATE;

GO

CREATE TRIGGER Products_UPDATE
	ON Products
	AFTER UPDATE
AS
DECLARE @DiscountPercent money, @ProductID	int
	BEGIN
		SELECT @DiscountPercent = DiscountPercent, @ProductID = ProductID
		FROM Inserted;
			IF (@DiscountPercent > 100 OR @DiscountPercent < 0)
				THROW 50001, 'DiscountPercent out of range.', 1;
				ROLLBACK TRAN;
			IF (@DiscountPercent BETWEEN 0 AND 1)
				UPDATE Products SET DiscountPercent = @DiscountPercent * 100 WHERE ProductID = @ProductID;
	END

When I test the above I get the following message:

------------------------
(1 row(s) affected)

(0 row(s) affected)
An error occured. Update was not successful.
Error number: 3609
Error message: The transaction ended in the trigger.
------------------------

When I check the table, the change did take place successfully. How do I solve this? Why am I getting this error? I haven't found anything useful in my search.

EDIT: I have tried with and without 'ROLLBACK TRAN'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-02-16 : 00:27:23

[code]CREATE TRIGGER dbo.trg_TableX_UPDATE
ON dbo.TableX
AFTER UPDATE,
INSERT
AS

SET NOCOUNT ON;

UPDATE x
SET ColX = CASE
WHEN x.ColX BETWEEN 0 AND 1 THEN 100 * x.ColX
ELSE x.ColX
END
FROM dbo.TableX AS x
INNER JOIN inserted AS i ON i.KeyCol = x.KeyCol;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

mallorz4
Starting Member

27 Posts

Posted - 2014-02-16 : 09:18:14

Thank you for the reply. I need to also throw an error message when a value is out of range.

What I scripted operates correctly, it just also throws that error. I think I will start a new thread on that subject.

EDIT: Or not, I just tried it again this morning and it worked. Not sure what that was about last night.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources