I have to write a SQL statement to create a trigger called "ValidOrder" checking the value of a new order inserted into the OrderDetails table is greated that $100. If the value is less that $100, the entry needs to be rolled back.

CREATE TABLE OrderDetails 

(

OrderId INT identity(1, 1), 

OrderName VARCHAR(50), 

OrderValue MONEY

)
GO

CREATE TRIGGER dbo.ValidOrder ON dbo.OrderDetails
AFTER INSERT, UPDATE
AS
BEGIN
 SET NOCOUNT ON;

 BEGIN TRANSACTION

 DECLARE @OrderValue FLOAT

 SELECT @OrderValue = OrderValue
 FROM inserted

 IF @OrderValue > 100
 BEGIN
  COMMIT TRANSACTION

  PRINT 'Record inserted'
 END
 ELSE
 BEGIN
  ROLLBACK TRANSACTION

  PRINT 'Record rolled back as order value is less than $100'
 END
END
GO

INSERT INTO OrderDetails (OrderName, OrderValue)
VALUES ('Order1', 55)

/*
Record rolled back as order value is less than $100
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
*/
INSERT INTO OrderDetails (OrderName, OrderValue)
VALUES ('Order1', 500)
 /*
Record inserted

(1 row(s) affected)
*/