Hi,

Bit of a newbie so please forgive my language / ignorance!!

I am trying to create a trigger that fires for an INSERT. There are two tables A and B. Basically the trigger needs to fire if the insert into one column in B is greater than that of A. I have got this far:

CREATE TRIGGER insert_into_b BEFORE insert ON B
REFERENCING NEW AS new_row
FOR EACH ROW
BEGIN
DECLARE b_C2 SMALLINT;
DECLARE a_bar SMALLINT;
SET b_C2 = new_row.C2;
SET a_bar = 15;
IF b_C2 >= a_bar THEN
SET new_row.c2= 1;
END IF;
END

This works and does what it should (to an extent - experimenting it simply sets value to 1 if too high) other than I want the value of a_bar to be set by MAX(a.bar) rather than just setting the value here. I assume I need to JOIN table A somehow but 3 hours later no joy. Also once this is sorted I assume I can change the "SET new_row.c2= 1; " bit to use RAISERROR instead right?

Any help appreciated !

Leebach.

Hi

I really do not understand how your script worked for your test.
Syntax for Create Trigger is

CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[ ...n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [ ...n ]
} ]
sql_statement [ ...n ]
}
}

For triggers, you need to use 2 special system tables, ie INSERTED and DELETED which hold the information about the rows which triggered the triggers to be executed. By the name you can imagine what they supposed to hold.

This is an example of INSERT trigger.

Go through the example and try it out please.

terrypack, thanks very much for the reply, very kind.

I have tried, changing bits where I think I need to (blindly admittedly!). I am getting the error 'syntax error near FROM on line 21').

Basically, 2 tables A and B. Columns on B are C1 and C2. I need to ensuer C2 is no greater than the max in a cloum from table A.

My code so far is:

CREATE TRIGGER someschema.insertB ON someschema.B
FOR INSERT
AS

/*
Comments
*/

DECLARE @C1 Varchar(1)
DECLARE @C2 SMALLINT
DECLARE @sComments Varchar(200)

BEGIN
-- Put all the transaction records in cursor
DECLARE Test_Cursor CURSOR FOR
SELECT C1,C2
FROM Inserted

-- Fetch one record at a time
OPEN Test_Cursor
FETCH NEXT FROM Test_Cursor INTO @C1, @C2

-- Loop through cursor until finished
WHILE @@FETCH_STATUS = 0
BEGIN


IF(@C2 = '20') -- or something else
BEGIN

-- Do something

If @@Error <> 0
BEGIN
SET @sComments='Specify Error Reason1'
GOTO ERR_EXIT
END

END

FETCH NEXT FROM Test_Cursor INTO @colA,@colB,@colC

If @@Error <> 0
BEGIN
SET @sComments='Specify Error Reason2'
GOTO ERR_EXIT
END
END

CLOSE Test_Cursor
DEALLOCATE Test_Cursor
END

Normal_EXIT:
RETURN

ERR_EXIT:
CLOSE Test_Cursor
DEALLOCATE Test_Cursor
RaisError (@sComments, 16, 1)
ROLLBACK TRAN
Return

Cheers, Leebach

Hi

Seems like you are trying this:
If not, advise me please.

hi,

I have coipied the code direct and just changes the name of the schema...

error says syntax error near FROM on line 26, which I think is this one:

FETCH NEXT FROM Test_Cursor INTO @C1, @C2

Thanks,

Lee.

First, your FETCH NEXT is not consistent. Before the loop it reflects the cursor definition, while inside the loop it does not. you might have changed it, but I go by what I see here.
Second, make sure to remove ELSE BEGIN --comments END from the first IF construct inside the WHILE loop, if you don't have an alternate logic.
Third, if the error says '...near FROM', it means that the syntax error is before that statement.

Thanks Lee for letting me know your status.

Yes, you are right. I missed to remove @C3 at the end.

Therfore
will fix the issue.

Hi,

appreciate your patience on this one! Looks like below now... In brackets is bar (the column name in A for which I need to find the MAX value. Still saying syntax erro near from on line 26!



CREATE TRIGGER someschema.insertB ON someschema.B
FOR INSERT
AS

/*
Comments
*/

DECLARE @C1 Varchar(1)
DECLARE @C2 SMALLINT
DECLARE @sComments Varchar(200)
DECLARE @C3 SMALLINT -- max value in table A

BEGIN
-- capture the max value in the table A
SELECT @C3=Max(bar)
FROM A

-- Put all the transaction records in cursor
DECLARE Test_Cursor CURSOR FOR
SELECT C1,C2
FROM Inserted

-- Fetch one record at a time
OPEN Test_Cursor
FETCH NEXT FROM Test_Cursor INTO @C1, @C2

-- Loop through cursor until finished
WHILE @@FETCH_STATUS = 0
BEGIN

--I need to ensuer C2 is no greater than the max in a cloum from table A
IF(@C2 > @C3)
BEGIN
SET @sComments='Validation failed: @C2 > @C3'
GOTO ERR_EXIT
END

FETCH NEXT FROM Test_Cursor INTO @colA,@colB

If @@Error <> 0
BEGIN
SET @sComments='Specify Error Reason2'
GOTO ERR_EXIT
END
END

CLOSE Test_Cursor
DEALLOCATE Test_Cursor
END

Normal_EXIT:
RETURN

ERR_EXIT:
CLOSE Test_Cursor
DEALLOCATE Test_Cursor
RaisError (@sComments, 16, 1)
ROLLBACK TRAN
Return

Hi Lee

I don't think you corrected it correctly.

Change the 2nd Fetch instruction

from
to
The error occurred because the @colA and @colB were not declared.

Hi,

thanks, I have changed (I think) as you said... same error (near 'FROM' line 26). I now have:

CREATE TRIGGER someschema.insertB ON someschema.B
FOR INSERT
AS

/*
Comments
*/

DECLARE @C1 Varchar(1)
DECLARE @C2 SMALLINT
DECLARE @sComments Varchar(200)
DECLARE @C3 SMALLINT -- max value in table A

BEGIN
-- capture the max value in the table A
SELECT @C3=Max(bar)
FROM A

-- Put all the transaction records in cursor
DECLARE Test_Cursor CURSOR FOR
SELECT C1,C2
FROM Inserted

-- Fetch one record at a time
OPEN Test_Cursor
FETCH NEXT FROM Test_Cursor INTO @C1, @C2

-- Loop through cursor until finished
WHILE @@FETCH_STATUS = 0
BEGIN

--I need to ensuer C2 is no greater than the max in a cloum from table A
IF(@C2 > @C3)
BEGIN
SET @sComments='Validation failed: @C2 > @C3'
GOTO ERR_EXIT
END

FETCH NEXT FROM Test_Cursor INTO @C1,@C2

If @@Error <> 0
BEGIN
SET @sComments='Specify Error Reason2'
GOTO ERR_EXIT
END
END

CLOSE Test_Cursor
DEALLOCATE Test_Cursor
END

Normal_EXIT:
RETURN

ERR_EXIT:
CLOSE Test_Cursor
DEALLOCATE Test_Cursor
RaisError (@sComments, 16, 1)
ROLLBACK TRAN
Return