Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Trigger based on a changed column

SozuaApr 14 2015 — edited Apr 15 2015

Hi

I need some help on coding a trigger.

I want that if a specific column in a table gets value inserted / updated it activate a trigger.

Example:

I have a table which stores the users:

Table name: USERS

Columns:    USERNAME | NUMBER |     CREATE_DATE         |LISTNUMBER | EMAIL_ADDRESS

Rows:               user1           23        04-APR-15 06.15.45.00PM       12          some1@email.com

                        user2           46        04-APR-15 06.15.45.00PM       12           some2@email.com

                        user3           35        07-APR-15 09.10.35.00PM       11         some3@email.com

And in a page in my application the ADMI can change or insert the values for the column NUMBER in the USERS table. Important is that he can change or insert the values for many users at the same time. He enters one or more values in a form page and than just press apply change button.

And if he presses the button I want that the trigger is activated before the values are inserted.

The trigger should save the values from the row where the value in the column NUMBER is changed into another table. The trigger should be based only on the column NUMBER, so only than when the column NUMBER gets changed.

So if the Admin change the values for user1 from number 23 to 24 and for user3 from number 35 to 36 the trigger should store the changed values in a different table like this:

Table name: USERS_OLD

Columns:    USERNAME | NUMBER |        CHANGED_DATE     |LISTNUMBER

Rows:                user1           23                      12-APR-15                   12

                         user3           35                      12-APR-15                   11     

Something like this should happen:

insert into USERS_OLD values

(USERNAME, NUMBER,  SYSDATE,  LISTNUMBER);

I do not know how to code that the trigger is coditional an so on, it would be nice if someone can code an example or give examples.

Any help is appreciated.

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 13 2015
Added on Apr 14 2015
16 comments
8,216 views