-1

I'm currently writing a PLSQL script that needs to capture whenever one of the DML events has occurred in a table. I'm trying to do it all in one trigger to capture these events without splitting them up into three separate triggers if possible. Any suggestions?

codemonkey
  • 515
  • 2
  • 6
  • 18

2 Answers2

1

Here's an example; dbms_output call is probably not what you want to do, but - you never said what you do want. Perhaps logging something? Set it by yourself, this is just to show how to do it.

SQL> create table test (id number, name varchar2(20));

Table created.

SQL>
SQL> create or replace trigger trg_biud_test
  2    before insert or update or delete on test
  3    for each row
  4  begin
  5    if inserting then
  6       dbms_output.put_line('Inserting');
  7    elsif updating then
  8       dbms_output.put_line('Updating');
  9    elsif deleting then
 10       dbms_output.put_line('Deleting');
 11    end if;
 12  end;
 13  /

Trigger created.

SQL> set serveroutput on
SQL> insert into test (id, name) values (1, 'Little');
Inserting

1 row created.

SQL> update test set name = 'Foot' where id = 1;
Updating

1 row updated.

SQL> delete from test where id = 1;
Deleting

1 row deleted.

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • why do we need sepearate triggers when it can be done via one for all actions ? – Himanshu Aug 02 '19 at 17:37
  • @HimanshuAhuja - What "separate triggers"? That's just one trigger. – Justin Cave Aug 02 '19 at 17:39
  • I am not asking for this particular answer/query but in general. – Himanshu Aug 02 '19 at 17:46
  • As usual, the same problem can be solved in more than one way. This is a simple example. Imagine that real code is complex and takes e.g. 50 lines. Triple that, and you have 150 lines. Some people prefer maintaining simpler code units (the ones that fit their screen) so they split it into 3 separate triggers. On the other hand, you'd rather not put that much code into triggers anyway - create procedures instead (and put them into a package, when we're at it) and call them from trigger(s). – Littlefoot Aug 02 '19 at 17:50
  • umm...fair enough just that it consumes more space though less complex is the only reason mainly. – Himanshu Aug 02 '19 at 20:17
0

why not display a message or catch a value in trigger itself that would meant that insert/update/delete has happened.

refer this Create one trigger for multiple operations

Himanshu
  • 3,830
  • 2
  • 10
  • 29