Trigger in Oracle - increment column referenced with foreign key-CodePudding

I need to create a trigger in Oracle SQL. I have 2 entities with these attributes:

army

army_name VARCHAR(50) PRIMARY KEY
number_of_soliders INTEGER

solider

personal_number INTEGER PRIMARY KEY
solider_name VARCHAR(50) NOT NULL
army_name VARCHAR(50) REFERENCES army(army_name)

Now I need to create a trigger for the number_of_soliders. The default value is 0, and I need to increment this value by 1 every time solider is inserted, for particular army. So if a solider is inserted and references 'US Army', their number of soliders is incremented by one automatically.

Thanks alot

CodePudding user response：

You can create sequences in Oracle such as

CREATE SEQUENCE soldier_seq MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10;

When creating a new row in the table, reference the key0 field an increment of the sequence

insert into soldier (personnel_number, soldier_name, army_name) values (soldier_seq.nextval, "first and last Name", "USMC")

CodePudding user response：

Trigger:

SQL> create or replace trigger trg_ai_sol
  2    after insert or delete on soldier
  3    for each row
  4  begin
  5    if inserting then
  6       update army a set
  7         a.number_of_soldiers = a.number_of_soldiers   1
  8         where a.army_name = :new.army_name;
  9    elsif deleting then
 10       update army a set
 11         a.number_of_soldiers = a.number_of_soldiers - 1
 12         where a.army_name = :old.army_name;
 13    end if;
 14  end;
 15  /

Trigger created.

Testing:

SQL> select * From soldier;

no rows selected

SQL> select * from army;

ARMY_NAME    NUMBER_OF_SOLDIERS
------------ ------------------
US Army                       0
Another Army                  0

SQL> insert into soldier values (1, 'Little', 'US Army');

1 row created.

SQL> insert into soldier
  2    select 2, 'Foot', 'Another Army' from dual union all
  3    select 3, 'Oracle', 'US Army' from dual;

2 rows created.

SQL> select * From soldier;

PERSONAL_NUMBER SOLDIER_NAME         ARMY_NAME
--------------- -------------------- --------------------
              1 Little               US Army
              2 Foot                 Another Army
              3 Oracle               US Army

SQL> select * from army;

ARMY_NAME    NUMBER_OF_SOLDIERS
------------ ------------------
US Army                       2
Another Army                  1

SQL> delete from soldier where personal_number in (2, 3);

2 rows deleted.

SQL> select * From soldier;

PERSONAL_NUMBER SOLDIER_NAME         ARMY_NAME
--------------- -------------------- --------------------
              1 Little               US Army

SQL> select * from army;

ARMY_NAME    NUMBER_OF_SOLDIERS
------------ ------------------
US Army                       1
Another Army                  0

SQL>

CodePudding user response：

This is a particularly bad idea, though apparently popular. If you try to store something that can be calculated at run time, it is only a matter of time before the stored value is incorrect. Trust me on this. You should not be storing 'number_of_soldiers' at all. You can always calculate it

SQL> show user
USER is "SCOTT"
SQL> -- create the tables
SQL>  CREATE TABLE "SCOTT"."ARMY"
  2     (    "ARMY_NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE,
  3           CONSTRAINT "ARMY_PK" PRIMARY KEY ("ARMY_NAME")
  4    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  5    TABLESPACE "USERS"  ENABLE
  6     ) SEGMENT CREATION DEFERRED
  7    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  8   NOCOMPRESS LOGGING
  9    TABLESPACE "USERS" ;

Table created.

SQL> --
SQL>  CREATE TABLE "SCOTT"."SOLDIER"
  2     (    "COLUMN1" NUMBER(*,0) NOT NULL ENABLE,
  3          "SOLDIER_NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE,
  4          "ARMY_NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE,
  5           CONSTRAINT "SOLDIER_PK" PRIMARY KEY ("COLUMN1")
  6    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  7    TABLESPACE "USERS"  ENABLE,
  8           CONSTRAINT "SOLDIER_FK1" FOREIGN KEY ("ARMY_NAME")
  9            REFERENCES "SCOTT"."ARMY" ("ARMY_NAME") ENABLE
 10     ) SEGMENT CREATION DEFERRED
 11    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 12   NOCOMPRESS LOGGING
 13    TABLESPACE "USERS" ;

Table created.

SQL> -- load tables
SQL> -- load tables
SQL> insert into army values ('US ARMY');

1 row created.

SQL> insert into army values ('Canadian Army');

1 row created.

SQL> insert into soldier values (1,'Jody','US ARMY');

1 row created.

SQL> insert into soldier values (2,'Fred','US ARMY');

1 row created.

SQL> insert into soldier values (3,'Bob','US ARMY');

1 row created.

SQL> insert into soldier values (4,'Pierre','Canadian Army');

1 row created.

SQL> insert into soldier values (5,'Rocky','Canadian Army');

1 row created.
SQL> -- Do the query
SQL> select army_name,
  2         count(*)
  3  from soldier
  4  group by army_name
  5  order by army_name;

ARMY_NAME              COUNT(*)
-------------------- ----------
Canadian Army                 2
US ARMY                       3

2 rows selected.

SQL> -- clean up
SQL> drop table  soldier purge;

Table dropped.

SQL> drop table army purge;

Table dropped.

There are several other design issues you have, but this addresses only the immediate question.