sql trigger - if statement - Prevent update

Question

I want to create a trigger to prevent values being entered above a certain value.

I have read a little but cannot relate the question below to my own.

Trigger to fire only if a condition is met in SQL Server

Code:

ALTER TRIGGER Tgr_IQRating
  ON  dbo.Customer
  FOR UPDATE 
AS
BEGIN
   SET NOCOUNT ON;

   DECLARE @IQ int

   Select @IQ = IQRATING from dbo.customer

   IF (@IQ) > 150 
   BEGIN
       PRINT ('Cannot enter anything higher than 100')
   END

   ROLLBACK TRANSACTION
END

I've tried it like this

IF (IQRating) > 150 
BEGIN
   PRINT ('Cannot enter anything higher than 100')
END

ROLLBACK TRANSACTION

But get an error that the column cannot be found. Also, the below fails when I try an update.

IF (SELECT IQRating FROM dbo.customer) > 150 
BEGIN
    PRINT ('Cannot enter anything higher than 100')
END

Error:

Msg 512, Level 16, State 1, Procedure Tgr_IQRating, Line 16
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Any help would be great.

Thanks,

Jay.

Knaģis · Accepted Answer · 2013-05-24T09:49:24.203

1

You should be using the special "table" inserted to see the data that is being updated otherwise you are looking at the whole table which will contain other rows as well.

IF EXISTS(SELECT 1 FROM inserted WHERE IQRating > 150)
BEGIN
    PRINT ('Cannot enter anything higher than 150')
END

A better solution would be to use RAISERROR or in SQL Server 2012 THROW statement instead of PRINT so that the error message is sent back to whoever initiated the update.

edited May 24 '13 at 09:49

answered May 23 '13 at 18:25

Knaģis

20,827
7
66
80

1

Actually, those pseudo tables are called `Inserted` for the new values (after `INSERT` or `UPDATE`) and `Deleted` (for the rows deleted, or the old valuse prior to the `UPDATE`). There's no `Updated` pseudo table in SQL Server ... – marc_s May 23 '13 at 18:58
1

wow, I don't know how I managed to invent the "updated" table... Sorry! – Knaģis May 24 '13 at 09:50

score 1 · Answer 2 · answered May 23 '13 at 19:59

1

You really ought to use a constraint for this. It's more idiomatic to SQL.

Check Constraint

answered May 23 '13 at 19:59

score 0 · Answer 3 · answered May 23 '13 at 19:51

You have multiple problems. First you would rollback 100% of the time as the rollback is not in the IF block.

Select @IQ = IQRATING from dbo.customer

is your subquery problem. I assume customer has multiple records! And you should probably be referncing the inserted pseudotable instead anyway.

Third you seem to think a trigger will operate on only one row at time. It will operate in batches. A clue to this is that you are populating a scalar variable. It is irresponsible and unprofessional to assume there will ever only be one record in the update.

Do you also need to do this check on INSERT? Or is this data that only gets populated in an update?

It is unclear if you want the whole transaction to rollback if one record is bad or if you want find any bad records inserted and keep only those database. The code for each possiblity would be different.

Edit some of this is for SQL server (I did not see the mysql tag, but you refernced SQL server in the text so I made an assumption.) The code looks like T_SQL code though.

Hello, Thank you for all answers, each one helping me out. marc_s - I used the IF EXISTS(SELECT 1 FROM updated WHERE IQRating > 150) BEGIN PRINT ('Cannot enter anything higher than 150') END which worked. HLGEM - thank you for pointing out the rollback in the if statement. As you may have guessed, I am very new to SQL and programming/scripting in general and am playing about with new areas. Micah Bright -I think constraints would be the best move going forward as less work/coding is needed. — Jay, May 23 '13 at 20:13

sql trigger - if statement - Prevent update

3 Answers3