I have googled high and low for any opinion on this question with no success.

I noticed that many developers use triggers for getting sequence nextval and for populating columns of audit type information vs. including these columns into CRUD procedures.

Version 12 new column types aside, is there an argument to be made that the trigger method is a better practice?

Here is a sample that happens to be from an Application Express application but my question is general.

CREATE OR REPLACE TRIGGER BIU_PRJ_USER_AUTHS_TRG
BEFORE INSERT OR UPDATE ON PRJ_USER_AUTHS
REFERENCING OLD AS OLD NEW AS NEW 
FOR EACH ROW 
BEGIN

  IF INSERTING THEN
    :NEW.user_auth_id := prj_user_auth_seq.NEXTVAL;
    :NEW.created_by   := NVL(v('APP_USER'), USER);
    :NEW.created_date := SYSDATE;
  END IF;

  :NEW.updated_by   := NVL(v('APP_USER'), USER);
  :NEW.updated_date := SYSDATE;

END;

Maybe it is because I started using Oracle 9i when triggers were more of a last resort but my preference has been to not use triggers this way.

It seems to me that such a trigger is just one more database object to have to keep track of while building and maintaining an application vs. putting this code into the procedure that is populating the rest of the data during CRUD transactions on a table.


Thanks.

SQL> create table t (x int, y date default sysdate);

Table T created.

SQL> insert into t (x) values (1);

1 row inserted.

SQL> select x, y, sysdate from t;

         X Y                            SYSDATE
---------- ---------------------------- ----------------------------
         1 15-APR-16                    15-APR-16