Prev: CLR Store Proc OleDbPermissions to an AS400
Next: Delete Remote Files With FTP
From: R.A.M. on 14 Apr 2007 13:51
Hello,
I am learning T-SQL (SQL Server 2005). I have written such trigger:

SET ANSI_NULLS ON;

GO

SET QUOTED_IDENTIFIER ON;

GO

CREATE TRIGGER InsteadOfInsertPositions

ON Positions

INSTEAD OF INSERT

AS

BEGIN

SET NOCOUNT ON;

DECLARE Received CURSOR FORWARD_ONLY READ_ONLY FOR

SELECT i.Material, ms.Stock, s.Disabled, i.Quantity, ms.Quantity AS
QuantityIsStock, ms.MaxQuantity

FROM inserted AS i INNER JOIN Documents AS d ON i.Document = d.Number

INNER JOIN Materials AS m ON i.Material = m.Id

INNER JOIN Stocks AS s ON d.ReceivingStock = s.Id

INNER JOIN MaterialsInStocks AS ms ON i.Material = ms.Material AND
d.ReceivingStock = ms.Stock;

OPEN Received;

FETCH FIRST FROM Received;

WHILE @@FETCH_STATUS = 0

BEGIN

IF Received.Disabled

RAISERROR (N'Operations on stock %s are disabled!',

16, 1,

Received.Stock);

IF Received.QuantityInStock + Received.Quantity > Received.MaxQuantity

RAISERROR (N'Maximum level of %s in stock %s exceeded (max. %f)!',

16, 1,

Received.Material, Received.Stock, Received.MaxQuantity);

FETCH NEXT FROM Received;

END;

CLOSE Received;

DEALLOCATE Received;

END;

GO

The problem is that I got the following error:


Msg 4145, Level 15, State 1, Procedure InsteadOfInsertPositions, Line 19

An expression of non-boolean type specified in a context where a condition
is expected, near 'RAISERROR'.

Msg 102, Level 15, State 1, Procedure InsteadOfInsertPositions, Line 21

Incorrect syntax near 'Received'.

Msg 102, Level 15, State 1, Procedure InsteadOfInsertPositions, Line 26

Incorrect syntax near 'Received'.


Could you help me please? I am beginner.
Thanks
/RAM/




From: Erland Sommarskog on 14 Apr 2007 18:30
R.A.M. (r_ahimsa_m(a)poczta.onet.pl) writes:
> IF Received.Disabled

This is plain wrong for two reasons:

1) You cannot refer to the columns of a cursor like that.
2) There are no boolean values in T-SQL; bit values must be compared to
0 or 1.

But, no, I am not going to show you how to write a cursor loop, because
there is about zero reaosn to have one. And many reasons not to have one.
One is performance. Cursors are a good way to slow things down.

But you are on the wrong track. Cursors in a trigger is definitely to be
avoided. Triggers should execute as fast as possible, and cursors are
not means to make things quick. Instead to:

IF EXISTS (SELECT *
FROM inserted AS i
JOIN Documents AS d ON i.Document = d.Number
JOIN Materials AS m ON i.Material = m.Id
JOIN Stocks AS s ON d.ReceivingStock = s.Id
WHERE s.Disabled = 1)
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('There is an operation on a disabled stock', 16, 1)
REURN
END

Right, we did not get to know which stock that was a disabled. There are
ways to handle this, but it is not really newbie stuff, so I leave that for
now.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 | 
Pages: 1
Prev: CLR Store Proc OleDbPermissions to an AS400
Next: Delete Remote Files With FTP