SQL statement. need help understanding it

Question

I am findiing it hard to understand what this code is doing. Could someone please help me understand this code line by line so that I can understand what it is doing.

CREATE TRIGGER LowCredit ON Order
AFTER INSERT
AS

DECLARE @creditrating tinyint

SELECT @creditrating = v.CreditRating
FROM Customer c INNER JOIN inserted i
ON c.custID = i.custID

IF @creditrating = 5
BEGIN

    RAISERROR ('This customers''s credit rating
    is too low to accept new orders.’)

    ROLLBACK TRANSACTION

END

Nix · Accepted Answer · 2011-06-01T11:56:37.667

It is checking to see if the credit rating is a certain value, if its too low is raising an error and rolling back the transaction.

--Declare a trigger with name `LowCredit` on table `Order`, 
--run the trigger after 
CREATE TRIGGER LowCredit ON Order  
insert.
AFTER INSERT

AS
--start definition
--declare int
DECLARE @creditrating tinyint

--select from existing customer record the
-- inserted rows credit ranking (by custID)
-- inserted is the vt containing the changed rows
SELECT @creditrating = v.CreditRating
   FROM Customer c INNER JOIN inserted i
ON c.custID = i.custID
--if lower than 5 roll back
IF @creditrating = 5
BEGIN
--raise error to the session
RAISERROR ('This customers''s credit rating
is too low to accept new orders.’)
--roll back transaction
ROLLBACK TRANSACTION

END

-1 As per my issues in my answer. The code is wrong even if you are correct. — gbn, Jun 02 '11 at 07:27
He didn't ask me to find a bug, he asked to explain the code. — Nix, Jun 02 '11 at 12:09

score 1 · Answer 2 · answered Jun 01 '11 at 11:50

1

The ROLLBACK in the trigger will prevent the insert of the row in Order if the customer's credit rating is too low...

inserted is a virtual table containing the inserted row(s).

answered Jun 01 '11 at 11:50

David M

71,481
13
158
186

score 1 · Answer 3 · answered Jun 01 '11 at 11:56

1

The error is on line 7. Replace v. with i. or c..

answered Jun 01 '11 at 11:56

Anthony Faull

17,549
5
55
73

score 0 · Answer 4 · edited May 23 '17 at 12:26

INSERTED is a special table that contains a copy of new (or changed for UPDATE) rows in the Order table

This is used to look into the Customer table to find the credit rating.

If the credit rating is too low, an error is thrown

Some issues:

it won't handle multiple row (eg many ORDERs in one go). The actual credit rating being checked will be a random row out of however many are inserted
no TRY/CATCH block to give better error handling and stop the trigger being batch aborting
missing SET NOCOUNT ON will break most clients at some point

Notes:

the trigger is part of the INSERT statement scope and the associated transaction

score 0 · Answer 5 · answered Jun 01 '11 at 11:53

Its checking "CreditRating" of Customer whose order is being created if Rating is less then 5 and error will be raised and transaction will be Rollback. I hope coder has used Begin Transaction somewhere else other wise RollBack transaction without Begin will give error

SQL statement. need help understanding it

5 Answers5