Create Insert Trigger

I've used the first to create an Update trigger for auditing changes to users permissions in a SyBase ASA Anywhere database.

The problem is that we aren't capturing data on newly created users.

I'm trying to add either a completely separate trigger or a combined trigger to capture both inserts and updates.  The second statement to create an insert trigger is failing with:

Line 1, Column 1
Could not Execute statement.
ASA Error -131: Syntax error near 'OF' on line 1

Any idea what I'm doing wrong?
CREATE TRIGGER "DBA"."My_Field" AFTER UPDATE OF "My_Field"
    ORDER 3 ON "DBA"."USER_Data"
	REFERENCING OLD AS OrigVal NEW AS NewVal 
	FOR EACH ROW
BEGIN
        Insert into Narc_USER_INFO_Change (user_id,Field_Changed,New_Value,Original_Value,Changed_By)
        Values(OrigVal.user_id, 'My_Field',  COALESCE(NewVal.My_Field,'0'),  COALESCE(OrigVal.My_Field,'0'), CURRENT USER); 
END;
-----------------------------------------
CREATE TRIGGER "DBA"."My_Field_INSERT" AFTER INSERT OF "My_Field"
    ORDER 4 ON "DBA"."USER_Data"
	REFERENCING NEW AS NewVal 
	FOR EACH ROW
BEGIN
        Insert into Narc_USER_INFO_Change (user_id,Field_Changed,New_Value,Original_Value,Changed_By)
        Values(OrigVal.user_id, 'My_Field',  COALESCE(NewVal.My_Field,'0'),  0, CURRENT USER); 
END;

Open in new window

LVL 38
Jim P.Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

grant300Commented:
I don't believe the "OF" qualifier on the trigger is valid for inserts.  This makes sense since you must insert an entire record, even if some of the fields are null.  An update can update a single field or any combination of fields so the ability to tell which are referred to in the SET clause of the UPDATE is handy.

A further problem is that you are referencing OrigVal.user_id which does not exist in the INSERT situation.  OrigVal was aliased to "OLD" in the update trigger and, of course, has no meaning and is not available in the INSERT trigger.

What you may want to do is change the INSERT/VALUES to an INSERT/SELECT and add a WHERE clause to test if the inserted value of My_Field is non-null.  That way you avoid writing an audit record when a row is inserted without information you are interested in.

Regards,
Bill
0
Jim P.Author Commented:
Trying it as below I now get the error
---------------------------
ASA Error -142 Correlation name 'INSERTED' not found
---------------------------

When I run an update statement on the table.

Did SyBase even bothering to check there help manual?
CREATE TRIGGER "DBA"."Total_Narc_Trigger" ON "DBA"."USER_INFORMATION"
    FOR INSERT, UPDATE
	AS 
    IF UPDATE(user_loan_limit_amt)
    BEGIN
        Insert into Narc_USER_INFO_Change (user_id,Field_Changed,New_Value,Original_Value,Changed_By)
        Values(INSERTED.user_id, 'user_loan_limit_amt',  COALESCE(DELETED.user_loan_limit_amt,'0'), 
                 COALESCE(deleted.user_loan_limit_amt,'0'), CURRENT USER) 
    END
    IF UPDATE(max_loan_secured)
    BEGIN
        Insert into Narc_USER_INFO_Change (user_id,Field_Changed,New_Value,Original_Value,Changed_By)
        Values(INSERTED.user_id, 'max_loan_secured',  COALESCE(DELETED.max_loan_secured,'0'), 
                 COALESCE(deleted.max_loan_secured,'0'), CURRENT USER) 
    END

Open in new window

0
grant300Commented:
The INSERTED and DELETED correlation names are for Sybase ASE, not ASA.

Second problem: even if you were using ASE, you have to put scaler items into the VALUES clause of an INSERT.  INSERTED can have multiple rows so you have to use an INSERT/SELECT to make it work.

Go back to your old code with separate UPDATE and INSERT triggers.  You were very close on the INSERT trigger; you just need to reformulate it as an INSERT/SELECT and remove the reference to the OLD pseudo table.

I don't have an ASA instance, much less an 8.x version, handy so you may have to tweak the code very so slightly when you compile it.  Give these a try and see what you get.

Regards,
Bill
CREATE TRIGGER "DBA"."My_Field" AFTER UPDATE OF "My_Field"
       ORDER 3 ON "DBA"."USER_Data"
       REFERENCING OLD AS OrigVal NEW AS NewVal 
BEGIN
       Insert Narc_USER_INFO_Change
              (user_id,Field_Changed,New_Value,Original_Value,Changed_By)
       Select OrigVal.user_id, 'My_Field',  COALESCE(NewVal.My_Field,'0'),
              COALESCE(OrigVal.My_Field,'0'), CURRENT USER); 
END;
 
CREATE TRIGGER "DBA"."My_Field_INSERT" AFTER INSERT
       ORDER 4 ON "DBA"."USER_Data"
       REFERENCING NEW AS NewVal 
BEGIN
        Insert Narc_USER_INFO_Change
               (user_id,Field_Changed,New_Value,Original_Value,Changed_By)
        Select NewVal.user_id, 'My_Field', COALESCE(NewVal.My_Field,'0'),
               0, CURRENT USER;
END;

Open in new window

0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Jim P.Author Commented:
It choked on the NewVal as a correlation row on the insert.

ASA Error 142       Correlation name 'NewVal ' not found

This is just so frustrating. I'm going to work on this again tomorrow.
0
grant300Commented:
Yeah, it doesn't help that ASA support both the Watcom and the T-SQL syntaxes for a bunch of this stuff either.

You could try removing the alias to NewVal and just refer to NEW in the SQL, e.g. NEW.My_Field

Of course, it sure would be nice to know why it works in the UPDATE trigger but chokes in the INSERT trigger.

Regards,
Bill
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim P.Author Commented:
I tried the NEW. Similar problems.

I'm just going to to tell the end-user this can't be done in our current version of SyBase. They may moan and groan, but at this point, let 'em whine.

I can't upgrade or change as this is a delivered app. :-( Thanks for all the help.
0
grant300Commented:
That's unfortunate.  The problem is not that it can't be done; it is just a matter of finding the correct syntax.

Duoh!!!!  I just found the problem.

In the Insert/Select, we forgot to Select from the NewVal table so, of course, when it went to parse the statement, it could not find the correlation name.  Sorry about that.

In the code snippet below, I add the line "From NewVal;" to the Insert/Select which should solve your problem.

BTW, you will have the same problem with the Update trigger if you decide to rewrite it to use the Insert/Select syntax.

Regards,
Bill
CREATE TRIGGER "DBA"."My_Field_INSERT" AFTER INSERT
       ORDER 4 ON "DBA"."USER_Data"
       REFERENCING NEW AS NewVal 
BEGIN
        Insert Narc_USER_INFO_Change
               (user_id,Field_Changed,New_Value,Original_Value,Changed_By)
        Select NewVal.user_id, 'My_Field', COALESCE(NewVal.My_Field,'0'),
               0, CURRENT USER
          from NewVal;
END;

Open in new window

0
Jim P.Author Commented:
No joy. The original was an

Insert (<fields>)
Values(NewVal.fld, OrigVal.flds)

As shown below and was fine.  

The inserttrigger fails as an INSERT<-->VALUES or an INSERT<-->SELECT.
"Correlation name not found" is the error.
CREATE TRIGGER "user_loan_limit_amt" AFTER UPDATE OF "user_loan_limit_amt",
	ORDER 1 ON "DBA"."USER_INFORMATION"
	REFERENCING OLD AS OrigVal NEW AS NewVal 
	FOR EACH ROW
BEGIN
    Insert into Narc_USER_INFO_Change (user_id,Field_Changed,New_Value,Original_Value,Changed_By)
    Values(OrigVal.user_id, 'user_loan_limit_amt',  COALESCE(NewVal.user_loan_limit_amt,'0'),  COALESCE(OrigVal.user_loan_limit_amt,'0'), CURRENT USER); 
END;
------------------------------------------
 
CREATE TRIGGER "DBA"."user_loan_limit_amt_INSERT" AFTER INSERT
    ORDER 4 ON "DBA"."USER_INFORMATION"
    REFERENCING NEW AS NewVal 
    BEGIN
        Insert Narc_USER_INFO_Change
               (user_id,Field_Changed,New_Value,Original_Value,Changed_By)
        Values(NewVal."user_id", 'user_loan_limit_amt', COALESCE(NewVal."user_loan_limit_amt",'0'),'0', CURRENT USER);
    END;

Open in new window

0
grant300Commented:
Go back to the FOR EACH ROW syntax for the INSERT and see what happens.

Boy, this is starting to tick me off {-|

Oh, well, we will get it eventually.

Regards,
Bill
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.