Разработка базы данных из предметной области "Предприятие", страница 4

      "SOC_STRAH" NUMBER(6,2),

      "FOND_ZAN" NUMBER(6,2),

      "POD" NUMBER(6,2),

      "EMP_ID" NUMBER(6,0) NOT NULL ENABLE,

      "JOB_ID" NUMBER(6,0),

      "H_WORK" NUMBER(5,0),

       CONSTRAINT "SALARY_CON" PRIMARY KEY ("EMP_ID") ENABLE,

       CONSTRAINT "SALARY_EMP_FK" FOREIGN KEY ("EMP_ID")

        REFERENCES  "EMP" ("EMP_ID") ENABLE

   );

Commit;

5.2 Триггеры

Для того чтобы негкоторые поля сущности «Salary» вычислялись автоматически при обновлении старых или добавлении новых атрибутов (а конкретно атрибута Sal сущности Jobs) создадим такой триггер:

CREATE OR REPLACE TRIGGER  "SAL_COUNT"

AFTER UPDATE OR INSERT ON jobs

FOR EACH ROW

DECLARE

v_sal number(10,2);

BEGIN

v_sal:=:new.sal;

UPDATE salary SET

PENS_FOND=v_sal*0.02

, SOC_STRAH=v_sal*0.06

, FOND_ZAN=v_sal*0.05

, POD=v_sal*0.13

, SAL=v_sal-pens_fond-soc_strah-fond_zan-pod

WHERE :old.job_id=salary.job_id and :new.job_type='F' ;

UPDATE salary SET

PENS_FOND=v_sal*h_work*0.02

, SOC_STRAH=v_sal*h_work*0.06

, FOND_ZAN=v_sal*h_work*0.05

, POD=v_sal*h_work*0.13

, SAL=v_sal*h_work-pens_fond-soc_strah-fond_zan-pod

WHERE :old.job_id=salary.job_id and :new.job_type='T' ;

END ;

5.3 Заполнение таблиц

INSERT INTO HUMANS VALUES(1012,'Irene Mikkil','TY46',4570,987547);

INSERT INTO HUMANS VALUES(1001,'Genry Kurtis','AT61',2212,262711);

INSERT INTO HUMANS VALUES(1002,'Alan Smith','BS27',2782,223315);

INSERT INTO HUMANS VALUES(1003,'Bob Marly    ','JF28',3121,911142);

INSERT INTO HUMANS VALUES(1004,'Jack Carter','NT94',8635,221343);

INSERT INTO HUMANS VALUES(1005,'Tony Stark','VS21',6222,330602);

INSERT INTO HUMANS VALUES(1006,'Jeff Blue','AM81',7345,992784);

INSERT INTO HUMANS VALUES(1007,'Piter Griffin','VS35',2201,112213     );

INSERT INTO HUMANS VALUES(1008,'Gregory Simpson','AM22',1117,220317);

INSERT INTO HUMANS VALUES(1009,'Shanta Vollman','FG02',4567,987544);

INSERT INTO HUMANS VALUES(1010,'Kevin Mourgos','AR45',4568,987545);

INSERT INTO HUMANS VALUES(1011,'Julia Nayer','QW78',4569,987546);

INSERT INTO HUMANS VALUES(1013,'James Landry','UI81',4571,987548);

INSERT INTO HUMANS VALUES(1014,'Steven Markle','OP74',4572,987549);

INSERT INTO HUMANS VALUES(1015,'Laura Bissot','KL11',4573,987550);

INSERT INTO EMP  VALUES(1001,  20001,10);

INSERT INTO EMP  VALUES(1002,  20001,10);

INSERT INTO EMP  VALUES(1003,  20001,10);

INSERT INTO EMP  VALUES(1004,  20002,10);

INSERT INTO EMP  VALUES(1005,  20002,20);

INSERT INTO EMP  VALUES(1006,  20003,30);

INSERT INTO EMP  VALUES(1012,  20006,20);

INSERT INTO EMP  VALUES(1007,  20003,30);

INSERT INTO EMP  VALUES(1008,  20003,30);

INSERT INTO EMP  VALUES(1009,  20004,10);

INSERT INTO EMP  VALUES(1010,  20005,20);

INSERT INTO EMP  VALUES(1011,  20005,20);

INSERT INTO EMP  VALUES(1013,  20007,20);

INSERT INTO EMP  VALUES(1014,  20008,40);

INSERT INTO EMP  VALUES(1015,  20008,40);

INSRT INTO DEPTS VAULUES(10,'Marketing',1006,101,222222);

INSRT INTO DEPTS VAULUES(20,'Purchasing',1009,102,222223);

INSRT INTO DEPTS VAULUES(30,'Human Resources',1013,103,222224);

INSRT INTO DEPTS VAULUES(40,'Shipping',1014,104,222225);

INSERT INTO SALARY VALUES(7400,200,600,500,1300,1001,20001,NULL,10000,2600);

INSERT INTO SALARY VALUES(29580,680,2040,1700,4420,1005,20002,170,38420,8840);

INSERT INTO SALARY VALUES(5254,142,426,355,923,1007,20003,NULL,7100,1846);

INSERT INTO SALARY VALUES(15720,408,1224,1020,2652,1010,20005,120,21024,5304);

INSERT INTO SALARY VALUES(7400,200,600,500,1300,1002,20001,NULL,10000,2600);

INSERT INTO SALARY VALUES(26100,600,1800,1500,3900,1004,20002,150,33900,7800);

INSERT INTO SALARY VALUES(5254,142,426,355,923,1006,20003,NULL,7100,1846);

INSERT INTO SALARY VALUES(5254,142,426,355,923,1008,20003,NULL,7100,1846);

INSERT INTO SALARY VALUES(6364,172,516,430,1118,1012,20006,NULL,8600,2236);