Re: Help writing simple trigger.

From: <suisum_at_ecn.ab.ca>
Date: 27 Feb 99 21:22:31 GMT
Message-ID: <36d86217.0@ecn.ab.ca>

Please try this:

SQL> CREATE TABLE test2
2 (itemno NUMBER(5) NOT NULL

  3       CONSTRAINT test2_pk PRIMARY KEY,
  4     cost   NUMBER(6, 2) NOT NULL,
  5     discount  NUMBER(4, 2) DEFAULT 0 NOT NULL,
  6     create_user VARCHAR2(15) NOT NULL,
  7     create_time DATE   NOT NULL,
  8     modify_user VARCHAR2(15),
  9     modify_time DATE);

Table created.

SQL> 
SQL> REM ==============================
SQL> REM capture username and sysdate.
SQL> REM ==============================
SQL> 
SQL> PROMPT Creating audit_info_tr .........

Creating audit_info_tr .........
SQL> CREATE OR REPLACE TRIGGER audit_info_tr   2 BEFORE INSERT OR UPDATE ON test2
  3 FOR EACH ROW
  4 BEGIN
  5
  5 IF INSERTING THEN
  6 :new.create_user := user;
  7 :new.create_time := sysdate;
  8 ELSIF UPDATING THEN
  9 :new.modify_user := user;
10 :new.modify_time := sysdate;
11 END IF;
12 END;
13 /

Trigger created.

SQL> 
SQL> REM ==============================
SQL> REM Test data.
SQL> REM ==============================
SQL> 
SQL> insert into test2

  2 (itemno, cost, discount)
  3 values
  4 (1, 1, 0.5)
  5 /

1 row created.

SQL>
SQL> insert into test2
  2 (itemno, cost, discount)
  3 values
  4 (2, 2, 0.5)
  5 /

1 row created.

SQL>
SQL> update test2
  2 set cost = 3
  3 where itemno = 2
  4 /

1 row updated.

SQL> select * from test2;

ITEMNO COST DISCOUNT CREATE_USER CREATE_TI MODIFY_USER MODIFY_TI
--------- --------- --------- --------------- --------- ---------------

        1         1        .5 SUISUM          27-FEB-99
        2         3        .5 SUISUM          27-FEB-99 SUISUM

27-FEB-99 tim.mcconechy_at_runtime.dk wrote:

: This trigger updates user_name and lastedit_date on a table during and update
: or insert operation for each row and maybe fill in the pk with a sequence
: number.... CREATE TRIGGER UPD_TRG BEFORE INSERT OR UPDATE ON dbstruct
: BEGIN user_name=user lastedit_date=sysdate recno=dbstructseq.nextval END;

: When I write my function I get table is mutating error.
: It should operate in the same transaction as the insert update.
Received on Sat Feb 27 1999 - 15:22:31 CST