Friday, March 23, 2012

Please explain this SQL Trigger

I am new to triggers.
I am unsure why on the SELECT statement the ROLLBACK is enforced. I thought this would be only for when an attempt is made to insert the data. Also what is the pupose of IF @.counter = 1
When does this counter change value?

USE Northwind
GO
CREATE TRIGGER PriceCheck
ON [Order Details]
FOR INSERT
AS
DECLARE @.counter int
SET @.counter= @.@.ROWCOUNT
DECLARE @.unitprice money
DECLARE order_details_insert_cursor CURSOR FOR
SELECT Unitprice
FROM inserted
IF @.counter = 1
BEGIN
IF(SELECT UnitPrice FROM inserted) > 300
-- If the price entered is greater than 300
BEGIN
--print a warning
PRINT 'Cannot enter price greater than 300'
--Take back the command
ROLLBACK TRANSACTION
END
END
ELSE
BEGIN
OPEN order_details_insert_cursor
FETCH NEXT FROM order_details_insert_cursor INTO @.unitprice
WHILE @.@.FETCH_STATUS = 0
BEGIN
if @.unitprice > 300
-- If the price entered is greater than 300
BEGIN
--print a warning
PRINT 'Cannot enter price greater than 300'
--Take back the command
ROLLBACK TRANSACTION
RETURN
--Exit trigger immediately
END

FETCH NEXT FROM order_details_insert_cursor INTO @.unitprice
END

CLOSE order_details_insert_cursor
END

DEALLOCATE order_details_insert_cursor
The select statement checks to see what the inserted value is, and ifit's greater than 300, its rolling back the attempted insert as beingoutside of the range of allowed values. As an aside, this isn'treally a good use of a trigger. A check constraint would be justas good, and would perform better.
The counter is set to the number of rows being inserted, so it'll beequal to the number of rows in the set being inserted into thetable. Theoretically, it could have a different value for everyattempted insert.

|||Thanks - I am trying to follow the logic of it and wondered if it works from top to bottom how does it come back to evaluate the counter? I can't see a looping structure in there.
Also could you explain what is meant by 'inserted' as in SELECT Unitprice
FROM inserted
Is 'inserted' a reserved word. Usually I would expect to see a table name there.|||The counter is set when the trigger is run. It isn't changedduring the execution of the of the trigger, so the value that isinitally set is the only value that it'll have until the next time thatthe trigger is fired.
"inserted" is a table, as you would expect. "inserted" and"deleted" are the names of two special internal tables that SQL Serveruses specifically to expose data being changed by INSERT, UPDATE, andDELETE statements to triggers.

No comments:

Post a Comment