triggers

[pgmr4]

I am a new user to Oracle and I am trying to write a trigger with SQL PLUS.
I have an Oracle table with 25 fields, but want my trigger to fire when one of eight fields changes. I am trying to do this with a WHEN statement. Please help.
Is there a better way, this will not compile.

CREATE OR REPLACE TRIGGER OFFENDER_TRG
AFTER INSERT OR UPDATE ON OFFENDER
FOR EACH ROW
WHEN (NEW:ADDRESS <> OLD:ADDRESS)
OR (NEW:CITY <> OLD:CITY)
OR (NEW:STATE <> OLD:STATE)
OR (NEW:ZIP <> OLD:ZIP)
BEGIN
.....
END;

[skhanal]

You can put the column list in CREATE statement.

CREATE OR REPLACE TRIGGER OFFENDER_TRG
AFTER INSERT OR UPDATE OF ADDRESS, CITY, STATE, ZIP ON OFFENDER
FOR EACH ROW

BEGIN
.....
END;

[skhanal]

Also to refer to new and old rows, you have to use . not : like

NEW.CITY <> OLD.CITY

[pgmr4]

Thank you skhanal for your help. Here is my code. I am getting error (Trigger created with compilation errors). Can you tell me what I am doing wrong in SQL Plus?

CREATE OR REPLACE TRIGGER AP.OFFENDER_TEST_TRG
AFTER INSERT OR UPDATE OF SYSTEM_ID, OFFENDER_ID, SOCIAL_SECURITY_NUMBER,
FIRST_NAME, MIDDLE_NAME, LAST_NAME, ADDRESS_LINE_1, ADDRESS_LINE_2,
CITY, STATE, ZIP_CODE, COUNTY, STATE_ID, FBI_ID, CLOSURE_DATE, CLOSURE_REASON,
SUPERVISION, OFFICER, BIRTHDATE, HEIGHT_FEET, HEIGHT_INCHES, WEIGHT, HAIR_COLOR,
EYE_COLOR, SEX, RACE, MARITAL_STATUS, EMPLOYMENT_STATUS ON AP.AP_A_OFFENDER
FOR EACH ROW
BEGIN
INSERT INTO AP.AP_A_OFFENDER_TEST
VALUES(:NEW.SYSTEM_ID, :NEW.OFFENDER_ID, :NEW.SOCIAL_SECURITY_NUMBER,
:NEW.FIRST_NAME, :NEW.MIDDLE_NAME, :NEW.LAST_NAME, :NEW.ADDRESS_LINE_1,
:NEW.ADDRESS_LINE_2, :NEW.CITY, :NEW.STATE, :NEW.ZIP_CODE, :NEW.COUNTY,
:NEW.STATE_ID, :NEW.FBI_ID, :NEW.CLOSURE_DATE, :NEW.CLOSURE_REASON,
:NEW.SUPERVISION, :NEW.OFFICER, :NEW.BIRTHDATE, :NEW.HEIGHT_FEET,
:NEW.HEIGHT_INCHES, :NEW.WEIGHT, :NEW.HAIR_COLOR, :NEW.EYE_COLOR, :NEW.SEX,
:NEW.RACE, :NEW.MARITAL_STATUS, :NEW.EMPLOYMENT_STATUS);
END;
/
Thank you

[bveoracle]

Enter SHOW ERRORS after compilation. Post the errors if it isn't clear.

You can also look in USER_ERRORS I believe.

[skhanal]

What is the error?

Instead of using
INSERT INTO AP.AP_A_OFFENDER_TEST
VALUES (...)

Use
INSERT INTO AP.AP_A_OFFENDER_TEST (col1,...,coln)
VALUES (...)

You may be passing the values in wrong order or not enough number of values.