Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2010
    Posts
    5

    Unanswered: INSERT Trigger from 2 tables

    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.

  2. #2
    Join Date
    May 2004
    Posts
    7
    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.

    CREATE TRIGGER [schema_name].[trg_name_Insert] ON [schema_name].[table_name]
    FOR INSERT
    AS

    /*
    Comments
    */

    DECLARE @colA Varchar(4)
    DECLARE @colB Varchar(1)
    DECLARE @colC Varchar(1)
    DECLARE @sComments Varchar(200)

    BEGIN
    -- Put all the transaction records in cursor
    DECLARE Test_Cursor CURSOR FOR
    SELECT colA
    ,colB
    ,colC
    FROM Inserted

    -- Fetch one record at a time
    OPEN Test_Cursor
    FETCH NEXT FROM Test_Cursor INTO @colA,@colB,@colC

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


    IF(@colA = 'N') -- or something else
    BEGIN

    -- Do something

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

    END

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

    If @@Error <> 0
    BEGIN
    SET @sComments='Specify Error Reason'
    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
    Go through the example and try it out please.

  3. #3
    Join Date
    Jun 2010
    Posts
    5

    still trying

    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

  4. #4
    Join Date
    May 2004
    Posts
    7
    Hi

    Seems like you are trying this:
    PHP Code:
    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(C2)
    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
        
    ELSE
        
    BEGIN
            
    -- if the data is valid what do you want to do?
            -- 
    Put code here
            
    -- If you dont want to do anything
            
    -- just remove the ELSE BEGIN 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 
    (@sComments161)
    ROLLBACK TRAN
    Return 
    If not, advise me please.

  5. #5
    Join Date
    Jun 2010
    Posts
    5

    error with 'FROM'

    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.

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    May 2004
    Posts
    7
    Thanks Lee for letting me know your status.

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

    Therfore
    FETCH NEXT FROM Test_Cursor INTO @C1, @C2
    will fix the issue.

  8. #8
    Join Date
    Jun 2010
    Posts
    5

    still no joy

    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

  9. #9
    Join Date
    May 2004
    Posts
    7
    Hi Lee

    I don't think you corrected it correctly.

    Change the 2nd Fetch instruction

    from
    FETCH NEXT FROM Test_Cursor INTO @colA,@colB
    to
    FETCH NEXT FROM Test_Cursor INTO @C1,@C2
    The error occurred because the @colA and @colB were not declared.

  10. #10
    Join Date
    Jun 2010
    Posts
    5

    I must be missing somethin!

    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •