Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE VIEW PracProj
- (nazwisko, stanowisko, placa, data_zatr, nr_dzialu, Nazwa_proj, budzet, Data_startu)
- AS SELECT ENAME, JOB, SAL, HIREDATE, DEPTNO, PNAME, Budget, START_DATE
- FROM emp, proj, proj_emp
- WHERE emp.empno = proj_emp.empno and proj.projno = proj_emp.projno
- CREATE or replace TRIGGER PracProj_insert
- INSTEAD OF INSERT ON PracProj
- FOR EACH ROW
- DECLARE
- p NUMBER;
- r NUMBER;
- s NUMBER;
- numerProjektu NUMBER;
- numerPracownika NUMBER;
- projektRzad Proj%ROWTYPE;
- pracownikRzad emp%ROWTYPE;
- BEGIN
- SELECT Count(*) INTO p FROM Proj
- WHERE Proj.PNAME = :NEW.Nazwa_proj;
- IF p=0 THEN
- Select NVL(max(PROJNO)+1,1) into numerProjektu from Proj;
- INSERT INTO Proj VALUES(numerProjektu, :NEW.Nazwa_Proj, :NEW.budzet, :NEW.Data_startu, null);
- ELSE
- UPDATE Proj SET budget =:NEW.budzet, start_date =:NEW.Data_startu, end_date = null
- WHERE Proj.PNAME = :NEW.Nazwa_Proj;
- END IF;
- SELECT Count(*) INTO r FROM Emp
- WHERE Emp.ename = :NEW.nazwisko;
- IF r=0 THEN
- Select NVL(max(empno)+1,1) into numerPracownika from emp;
- INSERT INTO Emp VALUES(numerPracownika, :NEW.Nazwisko , :NEW.stanowisko, null, :NEW.data_zatr,:NEW.placa,null,:NEW.nr_dzialu);
- END IF;
- SELECT * INTO projektRzad
- FROM (SELECT * FROM proj WHERE PROJ.Pname = :NEW.Nazwa_Proj)
- WHERE ROWNUM = 1;
- SELECT * INTO pracownikRzad
- FROM (SELECT * FROM emp WHERE EMP.Ename = :NEW.nazwisko)
- WHERE ROWNUM = 1;
- numerProjektu := projektRzad.PROJNO;
- numerPracownika := pracownikRzad.EMPNO;
- SELECT Count(*) INTO s FROM PROJ_EMP
- WHERE PROJ_EMP.PROJNO = numerProjektu AND PROJ_EMP.EMPNO = numerPracownika;
- IF s=0 THEN
- INSERT INTO PROJ_EMP VALUES(numerProjektu, numerPracownika);
- END IF;
- end;
- /
- show errors;
- insert into PracProj
- values('ZZZdoTestu','CLERK',80000,sysdate,20,'PROJECT100',40000,sysdate);
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement