Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Yamini.

Asked: May 02, 2018 - 6:01 am UTC

Last updated: May 07, 2018 - 4:46 am UTC

Version: 11 g

Viewed 1000+ times

You Asked

Hi Team,

I have a table named "t". Please find the structure of the table:

CREATE TABLE t
( t_id number(5) ,
s_id number(5),
name char(20),
mgr_id number(5),
CONSTRAINT pk_t PRIMARY KEY(t_id, s_id)
);


insert into t values(1,12,'a',0);
insert into t values(2,12,'b',1);
insert into t values(3,11,'c',1);
insert into t values(4,11,'aa',2);
insert into t values(5,11,'d',4);
insert into t values(6,10,'e',null);
insert into t values(7,11,'ab',3);
commit;


I have a requirement wherein whenever any row got deleted/t_id column got updated then whatever value t_id column has,if that value is available in any row for
mgr_id column, then that column mgr_id needs to be updated to 0 (zero).
For ex:
if the row having t_id=2 gets deleted

delete from t where t_id=2;

then the script will look in the table and find any row having mgr_id=2 , if it exists then it needs to be updated to 0 which is mgr_id=0

update t set mgr_id=0 where mgr_id =2;

This needs to be updated on its own.
Can you please help me out in creation of script/trigger..

All your help will be highly appreciated.

and Connor said...

You can do with it triggers (but please read the whole answer)

SQL>
SQL> CREATE TABLE t
  2  ( t_id number(5) ,
  3  s_id number(5),
  4  name char(20),
  5  mgr_id number(5),
  6  CONSTRAINT pk_t PRIMARY KEY(t_id, s_id)
  7  );

Table created.

SQL>
SQL> insert into t values(1,12,'a',0);

1 row created.

SQL> insert into t values(2,12,'b',1);

1 row created.

SQL> insert into t values(3,11,'c',1);

1 row created.

SQL> insert into t values(4,11,'aa',2);

1 row created.

SQL> insert into t values(5,11,'d',4);

1 row created.

SQL> insert into t values(6,10,'e',null);

1 row created.

SQL> insert into t values(7,11,'ab',3);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL> create or replace
  2  trigger after_delete
  3  after delete
  4  on t
  5  begin
  6    lock table t in exclusive mode;
  7    update t
  8    set mgr_id = 0
  9    where mgr_id not in ( select t_id from t );
 10  end;
 11  /

Trigger created.
SQL>
SQL> select * from t;

      T_ID       S_ID NAME                     MGR_ID
---------- ---------- -------------------- ----------
         1         12 a                             0
         2         12 b                             1
         3         11 c                             1
         4         11 aa                            2
         5         11 d                             4
         6         10 e
         7         11 ab                            3

7 rows selected.

SQL> delete from t where t_id = 2;

1 row deleted.

SQL> select * from t;

      T_ID       S_ID NAME                     MGR_ID
---------- ---------- -------------------- ----------
         1         12 a                             0
         3         11 c                             1
         4         11 aa                            0
         5         11 d                             4
         6         10 e
         7         11 ab                            3

6 rows selected.



So the above is the easiest of trigger solutions - we have to lock the entire table to ensure no-one adds new managers etc whilst we are doing are cleanup. But it will scan the whole table. If you want, you could add some complexity to only consider the manager id's in question


SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL> drop trigger after_delete;

Trigger dropped.

SQL>
SQL> create or replace trigger after_delete
  2    for delete on t
  3      compound trigger
  4
  5    mgr_list sys.odcinumberlist := sys.odcinumberlist();
  6
  7    before each row is
  8    begin
  9      mgr_list.extend;
 10      mgr_list(mgr_list.count) := :old.t_id;
 11    end before each row;
 12
 13    after statement is
 14    begin
 15       lock table t in exclusive mode;
 16       update t
 17       set mgr_id = 0
 18       where mgr_id in ( select column_value from table(mgr_list) );
 19
 20    end after statement;
 21
 22  end ;
 23  /

Trigger created.

SQL>
SQL> select * from t;

      T_ID       S_ID NAME                     MGR_ID
---------- ---------- -------------------- ----------
         1         12 a                             0
         2         12 b                             1
         3         11 c                             1
         4         11 aa                            2
         5         11 d                             4
         6         10 e
         7         11 ab                            3

7 rows selected.

SQL> delete from t where t_id = 2;

1 row deleted.

SQL> select * from t;

      T_ID       S_ID NAME                     MGR_ID
---------- ---------- -------------------- ----------
         1         12 a                             0
         3         11 c                             1
         4         11 aa                            0
         5         11 d                             4
         6         10 e
         7         11 ab                            3

6 rows selected.


But to be honest, this strikes me as a more of a data modelling issue. It would seem to me that a correction to your data model for the primary key would be a better option. Then a simple 'on delete set null' plus a virtual column should do the trick


SQL>
SQL>
SQL> drop table t cascade constraints purge;

Table dropped.

SQL>
SQL> CREATE TABLE t
  2  ( t_id number(5) ,
  3  s_id number(5),
  4  name char(20),
  5  mgr_id number(5),
  6  CONSTRAINT pk_t PRIMARY KEY(t_id),
  7  constraint fk foreign key ( mgr_id ) references t ( t_id ) on delete set null
  8  );

Table created.

SQL>
SQL>
SQL>
SQL> insert into t values(1,12,'a',null);

1 row created.

SQL> insert into t values(2,12,'b',1);

1 row created.

SQL> insert into t values(3,11,'c',1);

1 row created.

SQL> insert into t values(4,11,'aa',2);

1 row created.

SQL> insert into t values(5,11,'d',4);

1 row created.

SQL> insert into t values(6,10,'e',null);

1 row created.

SQL> insert into t values(7,11,'ab',3);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select * from t;

      T_ID       S_ID NAME                     MGR_ID
---------- ---------- -------------------- ----------
         1         12 a
         2         12 b                             1
         3         11 c                             1
         4         11 aa                            2
         5         11 d                             4
         6         10 e
         7         11 ab                            3

7 rows selected.

SQL> delete from t where t_id = 2;

1 row deleted.

SQL> select * from t;

      T_ID       S_ID NAME                     MGR_ID
---------- ---------- -------------------- ----------
         1         12 a
         3         11 c                             1
         4         11 aa
         5         11 d                             4
         6         10 e
         7         11 ab                            3

6 rows selected.

SQL> alter table t add mgr_disp number as ( nvl(mgr_id,0));

Table altered.

SQL> select * from t;

      T_ID       S_ID NAME                     MGR_ID   MGR_DISP
---------- ---------- -------------------- ---------- ----------
         1         12 a                                        0
         3         11 c                             1          1
         4         11 aa                                       0
         5         11 d                             4          4
         6         10 e                                        0
         7         11 ab                            3          3

6 rows selected.

SQL>
SQL>
SQL>


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Pk

Gh, May 03, 2018 - 5:31 am UTC

Notice that you have changed the pk which initially was on (t_id, s_id) 

Connor McDonald
May 07, 2018 - 4:45 am UTC

Yes, but there is a reason for that.

If (t_id,s_id) is a primary key...then it strikes me as odd, that the referred to element (mgr_id) is not also a composite.

As I said - that looks like a data modelling issue.

We can't really tell without real column names and true nature of the data.

Trigger

Yamini Bhatia, May 03, 2018 - 9:29 am UTC

Hi Team,

Thank you so much for all your help.

2 trigger script you have provided are awesome. But these 2 triggers are working only in case there is a Delete. But what if we update t_id column value. As per requirement this trigger should work in both the cases whether we update t_id column or delete the entire row.

For example: If I update t_id column value from 3 to 8

update t set t_id=8 where t_id=3;

Then the record where mgr_id=3 should becomes 0(zero).As below:
Here
SQL> select * from t;

T_ID S_ID NAME MGR_ID
---------- ---------- -------------------- ----------
1 12 a 0
2 12 b 1
8 11 c 1
4 11 aa 2
5 11 d 4
6 10 e
7 11 ab 0


Also On Delete Set Null feature will not work as in that you have changed the primary key definition from composite primary key (t_id,s_id) to t_id only.
Connor McDonald
May 07, 2018 - 4:46 am UTC

I Would start with the second trigger, and adjust it for update.

If you get stuck, paste in where you get up to, along with your testing and we'll help out

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library