Trigger Question 1

[woogoo]

Hi,

I want to write a trigger that will ensure that values entered or updated into a series of columns are monotonic. But I can't seem to access the current column values!

Under SQL Server 2005 I thought the syntax was Inserted., Deleted. etc., but no. Can someone tell me what it is as there is no reference (I can find) in the BOL.

woogoo

 

[bborissov]

You should work with INSERTED and DELETED tables (which are accessible only in TRIGGERS) INSERTED table keep new values of the record and DELETED table keeps old values of the record.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[woogoo]

Hey thanks,

this is what I thought, but when I try something like If (Inserted.V2 < Inserted.V1) ...

I get an error saying it can't bind the columns.

woogoo

 

[bborissov]

Could you post the code from your trigger?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[woogoo]

Here it is:

CREATE TRIGGER MonotonicCheck
   ON  Prices FOR INSERT, UPDATE
AS 
BEGIN
	IF ((Inserted.P2 < Inserted.P1) OR (Inserted.P3 < Inserted.P2) OR (Inserted.P4 < Inserted.P3) OR (Inserted.P5 < Inserted.P4))
		RAISERROR ('Values are not Monotonically Increasing', 16, 1) ;
END
GO

And the resulting error output:

Msg 4104, Level 16, State 1, Procedure MonotonicCheck, Line 6
The multi-part identifier "Inserted.P2" could not be bound.
Msg 4104, Level 16, State 1, Procedure MonotonicCheck, Line 6
The multi-part identifier "Inserted.P1" could not be bound.
...
Removed for brevity.

I know it's something I'm doing, just not quite worked out what, I'm waiting for the head slapping moment.

woogoo

 

[bborissov]

INSERTED and DELETED are TABLES!
You can't use them that way.
try this:

CREATE TRIGGER MonotonicCheck
   ON  Prices FOR INSERT, UPDATE
AS
BEGIN
IF EXISTS(SELECT 1
              FROM  Inserted.P2 < Inserted.P1 OR
                    Inserted.P3 < Inserted.P2 OR 
                    Inserted.P4 < Inserted.P3 OR 
                    Inserted.P5 < Inserted.P4)
   RAISERROR ('Values are not Monotonically Increasing', 16, 1) ;
END
GO

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.