Go4Expert

Go4Expert (http://www.go4expert.com/)
-   Oracle (http://www.go4expert.com/forums/oracle-forum/)
-   -   Help With Triggers And Funcions In Sqldeveloper oracle (http://www.go4expert.com/forums/help-triggers-funcions-sqldeveloper-t28152/)

legend-x 8Apr2012 14:25

Help With Triggers And Funcions In Sqldeveloper oracle
 
I have crated the following tables and fill them with data:

Code:

CREATE TABLE IPALLILOS (
ID_IPALLILOU VARCHAR2(10) PRIMARY KEY NOT NULL,
HMEROMINIA_PROSLIPSIS DATE DEFAULT NOT NULL,
MISTHOS NUMBER(10,2) NOT NULL,
BONUS  NUMBER(10,2)
);

CREATE TABLE PROSOPIKA_STOIXEIA (
ID_IPALLILOU VARCHAR2(10) NOT NULL,
ONOMA VARCHAR2(10) NOT NULL,
EPONIMO VARCHAR2(20) NOT NULL,
IMEROMINIA_GENNISIS DATE DEFAULT,
FOREIGN KEY (ID_IPALLILOU)
REFERENCES IPALLILOS(ID_IPALLILOU)
);

CREATE TABLE APODOSI_ETOS (
ID_IPALLILOU VARCHAR2(10) NOT NULL,
KERDOS NUMBER(10,2) NOT NULL,
ETOS DATE DEFAULT NOT NULL,
FOREIGN KEY (ID_IPALLILOU)
REFERENCES IPALLILOS(ID_IPALLILOU)
);

create table paragwgikothta_ipallilou(
id_ipallilou varchar(10) NOT NULL
sunoliko_kerdos number(10,2) NOT NULL,
FOREIGN KEY (ID_IPALLILOU) REFERENCES IPALLILOS(ID_IPALLILOU)
);



create table katastash_ipallilou
(
id_ipallilou varchar(10) NOT NULL,
oikogeniakh_katastash VARCHAR(15),
paidia VARCHAR(15),
pososto_anaphrias NUMBER(3) NOT NULL,
FOREIGN KEY (ID_IPALLILOU) REFERENCES IPALLILOS(ID_IPALLILOU)
);

INSERT INTO IPALLILOS(ID_IPALLILOU,HMEROMINIA_PROSLIPSIS,MISTHOS,BONUS)
VALUES (001,TO_DATE('17-09-2008', 'DD-MM-YYYY'),550,110);

INSERT INTO IPALLILOS(ID_IPALLILOU,HMEROMINIA_PROSLIPSIS,MISTHOS,BONUS)
VALUES (002,TO_DATE('17-08-2002', 'DD-MM-YYYY'),750,150);

INSERT INTO IPALLILOS(ID_IPALLILOU,HMEROMINIA_PROSLIPSIS,MISTHOS) VALUES ('003',to_date('10-05-1998','DD-MM-YYYY'),'1200','98');
INSERT INTO IPALLILOS(ID_IPALLILOU,HMEROMINIA_PROSLIPSIS,MISTHOS) VALUES  ('004',to_date('07-08-2001','DD-MM-YYYY'),'1360','198');
INSERT INTO IPALLILOS(ID_IPALLILOU,HMEROMINIA_PROSLIPSIS,MISTHOS) VALUES  ('005',to_date('10-12-1994','DD-MM-YYYY'),'1400','102');

INSERT INTO PROSOPIKA_STOIXEIA(ID_IPALLILOU,ONOMA,EPONIMO,IMEROMINIA_GENNISIS)
VALUES (001,GIWRGOS,NTALARAS,TO_DATE('01-03-1965', 'DD-MM-YYYY'));


INSERT INTO PROSOPIKA_STOIXEIA(ID_IPALLILOU,ONOMA,EPONIMO,IMEROMINIA_GENNISIS)
VALUES (002,NIKOS,PAPPAS,TO_DATE('25-08-1974', 'DD-MM-YYYY'));

INSERT INTO  PROSOPIKA_STOIXEIA(ID_IPALLILOU,ONOMA,EPONIMO,IMEROMINIA_GENNISIS)  VALUES('003','KWSTANTINOS','PALAIKOSTAS','TO_DATE('25-04-1975'),'DD-MM-YYYY');
INSERT INTO  PROSOPIKA_STOIXEIA(ID_IPALLILOU,ONOMA,EPONIMO,IMEROMINIA_GENNISIS)  VALUES('004','KSIROS','SAVVAS','TO_DATE('15-02-1875'),'DD-MM-YYYY');
INSERT INTO  PROSOPIKA_STOIXEIA(ID_IPALLILOU,ONOMA,EPONIMO,IMEROMINIA_GENNISIS)  VALUES('005','KWSTANTINOS','PALAIOLOGOS','TO_DATE('04-09-1968'),'DD-MM-YYYY');

INSERT INTO APODOSI_ETOS(ID_IPALLILOU,KERDOS,ETOS)
VALUES (001, 300, TO_DATE('01-01-2009', DD-MM-YYY));


INSERT INTO APODOSI_ETOS(ID_IPALLILOU,KERDOS,ETOS)
VALUES (001, 600, TO_DATE('01-01-2011', DD-MM-YYY));


INSERT INTO APODOSI_ETOS(ID_IPALLILOU,KERDOS,ETOS)
VALUES (001, 900, TO_DATE('01-01-2010', DD-MM-YYY));


INSERT INTO APODOSI_ETOS(ID_IPALLILOU,KERDOS,ETOS)
VALUES (001, 700, TO_DATE('01-01-2008', DD-MM-YYY));

INSERT INTO APODOSI_ETOS(ID_IPALLILOU,KERDOS,ETOS)
VALUES (002, 700, TO_DATE('01-01-2011', DD-MM-YYY));

INSERT INTO APODOSI_ETOS(ID_IPALLILOU,KERDOS,ETOS)
VALUES (002, 400, TO_DATE('01-01-2010', DD-MM-YYY));

INSERT INTO APODOSI_ETOS(ID_IPALLILOU,KERDOS,ETOS)
VALUES (002, 900, TO_DATE('01-01-2009', DD-MM-YYY));

INSERT INTO APODOSI_ETOS(ID_IPALLILOU,KERDOS,ETOS)
VALUES (002, 100, TO_DATE('01-01-2008', DD-MM-YYY));


INSERT INTO APODOSI_ETOS(ID_IPALLILOU,KERDOS,ETOS) VALUES
('003','100000','2012');
INSERT INTO APODOSI_ETOS(ID_IPALLILOU,KERDOS,ETOS) VALUES
('003','150000','2011');
INSERT INTO APODOSI_ETOS(ID_IPALLILOU,KERDOS,ETOS) VALUES
('003','200000','2010');
INSERT INTO APODOSI_ETOS(ID_IPALLILOU,KERDOS,ETOS) VALUES
('003','180000','2009');
INSERT INTO APODOSI_ETOS(ID_IPALLILOU,KERDOS,ETOS) VALUES
('004','120000','2012');
INSERT INTO APODOSI_ETOS(ID_IPALLILOU,KERDOS,ETOS) VALUES
('004','110000','2011');
INSERT INTO APODOSI_ETOS(ID_IPALLILOU,KERDOS,ETOS) VALUES
('004','250000','2010');
INSERT INTO APODOSI_ETOS(ID_IPALLILOU,KERDOS,ETOS) VALUES
('004','170000','2009');
INSERT INTO APODOSI_ETOS(ID_IPALLILOU,KERDOS,ETOS) VALUES
('005','160000','2012');
INSERT INTO APODOSI_ETOS(ID_IPALLILOU,KERDOS,ETOS) VALUES
('005','130000','2011');
INSERT INTO APODOSI_ETOS(ID_IPALLILOU,KERDOS,ETOS) VALUES
('005','140000','2010');
INSERT INTO APODOSI_ETOS(ID_IPALLILOU,KERDOS,ETOS) VALUES
('005','190000','2009');

INSERT INTO PARAGWGIKOTHTA_IPALLILOU(ID_IPALLILOU,SUNOLIKO_KERDOS)
VALUES (001,250000);

INSERT INTO PARAGWGIKOTHTA_IPALLILOU(ID_IPALLILOU,SUNOLIKO_KERDOS)
VALUES (002,410000);

INSERT INTO PARAGWGIKOTHTA_IPALLILOU(ID_IPALLILOU,SUNOLIKO_KERDOS) VALUES ('003','110000');
INSERT INTO PARAGWGIKOTHTA_IPALLILOU(ID_IPALLILOU,SUNOLIKO_KERDOS) VALUES ('004','100000');
INSERT INTO PARAGWGIKOTHTA_IPALLILOU(ID_IPALLILOU,SUNOLIKO_KERDOS) VALUES ('005','150000');

INSERT INTO KATASTASH_IPALLILOU(ID_IPALLILOU,OIKOGENIAKH_KATASTASH,PAIDIA,POSOSTO_ANAPHRIAS)
VALUES(001, 'EGGAMOS',2,0);

INSERT INTO KATASTASH_IPALLILOU(ID_IPALLILOU,OIKOGENIAKH_KATASTASH,PAIDIA,POSOSTO_ANAPHRIAS)
VALUES(002, 'DIAZEUGMENOS',3,20);

INSERT INTO KATASTASH_IPALLILOU(ID_IPALLILOS,OIKOGENIAKH_KATASTASH,PAIDIA,POSOSTO_ANAPHRIAS) VALUES ('003','AGAMOS','00','0');
INSERT INTO KATASTASH_IPALLILOU(ID_IPALLILOS,OIKOGENIAKH_KATASTASH,PAIDIA,POSOSTO_ANAPHRIAS) VALUES ('004','EGAMOS','02','0');
INSERT INTO KATASTASH_IPALLILOU(ID_IPALLILOS,OIKOGENIAKH_KATASTASH,PAIDIA,POSOSTO_ANAPHRIAS) VALUES ('005','EGAMOS','03','45');

I need the following:

1. we have to create a funcion in which if in the table IPALLILOS the salary of an employee is less than 500 add 200 more.

2. Increase by 50 bonus for employees who have salary <= 1400 but the last year brought profit> = 1000 (using functions)

3. Bonus reduction at 150 for employees who the last 4 years brought profit of less than 3000 per year (using functions)

4. During data entry on the list on the table APODOSI_ETOS for each employee make sure to refresh the table paragwgikothta_ipallilou automatically using trigger (trigger based).

thanks in advance for the help.

xpi0t0s 17Apr2012 17:39

Re: Help With Triggers And Funcions In Sqldeveloper oracle
 
Sounds like homework. How far have you got and where are you stuck?


All times are GMT +5.5. The time now is 07:07.