Oracle PL/SQL Tutorial/Trigger/NEW OLD — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:45, 26 мая 2010
Содержание
- 1 :old and :new Pseudo-records, Example 1
- 2 :old and :new Pseudo-records, Example 2
- 3 Old and new value
- 4 Output new and old value in a before update trigger
- 5 Raise application error in a trigger in case of invalid new value
- 6 Reference new value with :NEW in a before insert or update trigger
- 7 REFERENCING OLD AS old NEW AS new
- 8 REFERENCING OLD AS old_values NEW AS new_values
- 9 Refernece an old value in :OLD after update trigger
:old and :new Pseudo-records, Example 1
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER TempDelete
2 BEFORE DELETE ON employee
3 FOR EACH ROW
4 DECLARE
5 v_TempRec employee%ROWTYPE;
6 BEGIN
7 /* This is not a legal assignment, since :old is not truly
8 a record. */
9 --v_TempRec := :old;
10
11 /* We can accomplish the same thing, however, by assigning
12 the fields individually. */
13 v_TempRec.first_name := :old.first_name;
14 v_TempRec.last_name := :old.last_name;
15
16 DBMS_OUTPUT.put_line(v_TempRec.first_name);
17 DBMS_OUTPUT.put_line(v_TempRec.last_name);
18 END TempDelete;
19 /
Trigger created.
SQL>
SQL> delete from employee;
Jason
Martin
Alison
Mathews
James
Smith
Celia
Rice
Robert
Black
Linda
Green
David
Larry
James
Cat
8 rows deleted.
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
:old and :new Pseudo-records, Example 2
SQL>
SQL> create sequence seq;
Sequence created.
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
3 rows selected.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER GenerateEmployeeID
2 BEFORE INSERT OR UPDATE ON employee
3 FOR EACH ROW
4 BEGIN
5 /* Fill in the ID field of employees with the next value from
6 the sequence.*/
7 SELECT seq.nextval
8 INTO :new.ID
9 FROM dual;
10 END GenerateEmployeeID;
11 /
Trigger created.
SQL>
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL> select * from employee;
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
1 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
2 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
3 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
4 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
5 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL> drop sequence seq;
Sequence dropped.
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
Old and new value
SQL>
SQL> CREATE TABLE game_player
2 (player_id NUMBER,
3 game_id NUMBER,
4 group_number NUMBER,
5 marked VARCHAR2(1) DEFAULT "N",
6 pcmac VARCHAR2(1) DEFAULT "N",
7 score NUMBER,
8 CONSTRAINT game_player_pk
9 PRIMARY KEY (player_id, game_id, group_number));
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER before_insert_row
2 BEFORE INSERT
3 ON game_player
4 FOR EACH ROW
5 BEGIN
6 DBMS_OUTPUT.put_line ("Before Insert Row");
7 DBMS_OUTPUT.put_line ("Old ID " || :OLD.player_id);
8 DBMS_OUTPUT.put_line ("New ID " || :NEW.player_id);
9 DBMS_OUTPUT.put_line ("Old ROWID " || ROWIDTOCHAR (:OLD.ROWID));
10 DBMS_OUTPUT.put_line ("New ROWID " || ROWIDTOCHAR (:NEW.ROWID));
11 END;
12 /
Trigger created.
SQL>
SQL> BEGIN
2 INSERT INTO game_player(player_id, game_id, group_number, marked, pcmac, score)
3 VALUES (1, 1, 1, "Y", "N", NULL);
4
5 UPDATE game_player
6 SET player_id = player_id;
7
8 DELETE game_player;
9 END;
10
11 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table game_player;
Table dropped.
Output new and old value in a before update trigger
SQL>
SQL> create table t ( x int, y int );
Table created.
SQL>
SQL> insert into t values ( 1, 1 );
1 row created.
SQL>
SQL> create or replace trigger t_bufer
2 before update on t for each row
3 begin
4 dbms_output.put_line( "old.x = " || :old.x ||", old.y = " || :old.y );
5 dbms_output.put_line( "new.x = " || :new.x ||", new.y = " || :new.y );
6 end;
7 /
Trigger created.
SQL> set serveroutput on
SQL> update t set x = x+1;
old.x = 1, old.y = 1
new.x = 2, new.y = 1
1 row updated.
SQL>
SQL> drop table t;
Table dropped.
SQL>
Raise application error in a trigger in case of invalid new value
SQL>
SQL> create table t ( x int );
Table created.
SQL>
SQL> create trigger t_trigger before insert on t for each row
2 begin
3 for x in ( select *
4 from dual
5 where :new.x > 10)
6 loop
7 raise_application_error( -20001, "check failed" );
8 end loop;
9 end;
10 /
Trigger created.
SQL> show errors
No errors.
SQL>
SQL> insert into t select 1 from all_users;
15 rows created.
SQL>
SQL> set autotrace traceonly statistics
SQL>
SQL> insert into t select 1 from all_users;
15 rows created.
Statistics
----------------------------------------------------------
16 recursive calls
15 db block gets
73 consistent gets
0 physical reads
0 redo size
924 bytes sent via SQL*Net to client
947 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
15 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL>
SQL>
Reference new value with :NEW in a before insert or update trigger
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> CREATE OR REPLACE TRIGGER employee_insert_update
2 BEFORE INSERT OR UPDATE ON employee
3 FOR EACH ROW
4 DECLARE
5 dup_flag INTEGER;
6 BEGIN
7 --Force all employee names to uppercase.
8 :NEW.first_name := UPPER(:NEW.first_name);
9 END;
10 /
Trigger created.
SQL>
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 JASON Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 ALISON Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 JAMES Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 CELIA Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 ROBERT Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 LINDA Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 DAVID Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 JAMES Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
REFERENCING OLD AS old NEW AS new
SQL>
SQL> CREATE TABLE game_player
2 (player_id NUMBER,
3 game_id NUMBER,
4 group_number NUMBER,
5 marked VARCHAR2(1) DEFAULT "N",
6 pcmac VARCHAR2(1) DEFAULT "N",
7 score NUMBER,
8 CONSTRAINT game_player_pk
9 PRIMARY KEY (player_id, game_id, group_number));
Table created.
SQL>
SQL>
SQL> -- explicit default old and new
SQL> CREATE OR REPLACE TRIGGER old_new_update
2 BEFORE update ON game_player
3 REFERENCING OLD AS old NEW AS new
4 FOR EACH ROW
5 BEGIN
6 DBMS_OUTPUT.PUT_LINE("Old marked = " || :old.marked);
7 DBMS_OUTPUT.PUT_LINE("New marked = " || :new.marked);
8 END;
9 /
Trigger created.
SQL>
SQL>
SQL>
SQL> drop table game_player;
Table dropped.
REFERENCING OLD AS old_values NEW AS new_values
SQL> CREATE TABLE game_player
2 (player_id NUMBER,
3 game_id NUMBER,
4 group_number NUMBER,
5 marked VARCHAR2(1) DEFAULT "N",
6 pcmac VARCHAR2(1) DEFAULT "N",
7 score NUMBER,
8 CONSTRAINT game_player_pk
9 PRIMARY KEY (player_id, game_id, group_number));
Table created.
SQL>
SQL>
SQL> -- changing the defaults
SQL> CREATE OR REPLACE TRIGGER old_new_delete
2 BEFORE delete ON game_player
3 REFERENCING OLD AS old_values NEW AS new_values
4 FOR EACH ROW
5 BEGIN
6 DBMS_OUTPUT.PUT_LINE("Old marked = " || :old_values.marked);
7 DBMS_OUTPUT.PUT_LINE("New marked = " || :new_values.marked);
8 END;
9 /
Trigger created.
SQL>
SQL>
SQL>
SQL> drop table game_player;
Table dropped.
Refernece an old value in :OLD after update trigger
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER employee_upd
2 AFTER UPDATE OF id ON employee
3 FOR EACH ROW
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE (":OLD.id" || :OLD.id);
6 END;
7 /
Trigger created.
SQL>
SQL> update employee set id = "1";
:OLD.id01
:OLD.id02
:OLD.id03
:OLD.id04
:OLD.id05
:OLD.id06
:OLD.id07
:OLD.id08
8 rows updated.
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.