Help With Triggers And Funcions In Sqldeveloper oracle
2 posters
Page 1 of 1
Help With Triggers And Funcions In Sqldeveloper oracle
I have crated the following tables and fill them with data:
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,MISTH OS,BONUS)
VALUES (001,TO_DATE('17-09-2008', 'DD-MM-YYYY'),550,110);
INSERT INTO IPALLILOS(ID_IPALLILOU,HMEROMINIA_PROSLIPSIS,MISTH OS,BONUS)
VALUES (002,TO_DATE('17-08-2002', 'DD-MM-YYYY'),750,150);
INSERT INTO IPALLILOS(ID_IPALLILOU,HMEROMINIA_PROSLIPSIS,MISTH OS) VALUES ('003',to_date('10-05-1998','DD-MM-YYYY'),'1200','98');
INSERT INTO IPALLILOS(ID_IPALLILOU,HMEROMINIA_PROSLIPSIS,MISTH OS) VALUES ('004',to_date('07-08-2001','DD-MM-YYYY'),'1360','198');
INSERT INTO IPALLILOS(ID_IPALLILOU,HMEROMINIA_PROSLIPSIS,MISTH OS) VALUES ('005',to_date('10-12-1994','DD-MM-YYYY'),'1400','102');
INSERT INTO PROSOPIKA_STOIXEIA(ID_IPALLILOU,ONOMA,EPONIMO,IMER OMINIA_GENNISIS)
VALUES (001,GIWRGOS,NTALARAS,TO_DATE('01-03-1965', 'DD-MM-YYYY'));
INSERT INTO PROSOPIKA_STOIXEIA(ID_IPALLILOU,ONOMA,EPONIMO,IMER OMINIA_GENNISIS)
VALUES (002,NIKOS,PAPPAS,TO_DATE('25-08-1974', 'DD-MM-YYYY'));
INSERT INTO PROSOPIKA_STOIXEIA(ID_IPALLILOU,ONOMA,EPONIMO,IMER OMINIA_GENNISIS) VALUES('003','KWSTANTINOS','PALAIKOSTAS','TO_DATE( '25-04-1975'),'DD-MM-YYYY');
INSERT INTO PROSOPIKA_STOIXEIA(ID_IPALLILOU,ONOMA,EPONIMO,IMER OMINIA_GENNISIS) VALUES('004','KSIROS','SAVVAS','TO_DATE('15-02-1875'),'DD-MM-YYYY');
INSERT INTO PROSOPIKA_STOIXEIA(ID_IPALLILOU,ONOMA,EPONIMO,IMER OMINIA_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_KER DOS)
VALUES (001,250000);
INSERT INTO PARAGWGIKOTHTA_IPALLILOU(ID_IPALLILOU,SUNOLIKO_KER DOS)
VALUES (002,410000);
INSERT INTO PARAGWGIKOTHTA_IPALLILOU(ID_IPALLILOU,SUNOLIKO_KER DOS) VALUES ('003','110000');
INSERT INTO PARAGWGIKOTHTA_IPALLILOU(ID_IPALLILOU,SUNOLIKO_KER DOS) VALUES ('004','100000');
INSERT INTO PARAGWGIKOTHTA_IPALLILOU(ID_IPALLILOU,SUNOLIKO_KER DOS) VALUES ('005','150000');
INSERT INTO KATASTASH_IPALLILOU(ID_IPALLILOU,OIKOGENIAKH_KATAS TASH,PAIDIA,POSOSTO_ANAPHRIAS)
VALUES(001, 'EGGAMOS',2,0);
INSERT INTO KATASTASH_IPALLILOU(ID_IPALLILOU,OIKOGENIAKH_KATAS TASH,PAIDIA,POSOSTO_ANAPHRIAS)
VALUES(002, 'DIAZEUGMENOS',3,20);
INSERT INTO KATASTASH_IPALLILOU(ID_IPALLILOS,OIKOGENIAKH_KATAS TASH,PAIDIA,POSOSTO_ANAPHRIAS) VALUES ('003','AGAMOS','00','0');
INSERT INTO KATASTASH_IPALLILOU(ID_IPALLILOS,OIKOGENIAKH_KATAS TASH,PAIDIA,POSOSTO_ANAPHRIAS) VALUES ('004','EGAMOS','02','0');
INSERT INTO KATASTASH_IPALLILOU(ID_IPALLILOS,OIKOGENIAKH_KATAS TASH,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.
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,MISTH OS,BONUS)
VALUES (001,TO_DATE('17-09-2008', 'DD-MM-YYYY'),550,110);
INSERT INTO IPALLILOS(ID_IPALLILOU,HMEROMINIA_PROSLIPSIS,MISTH OS,BONUS)
VALUES (002,TO_DATE('17-08-2002', 'DD-MM-YYYY'),750,150);
INSERT INTO IPALLILOS(ID_IPALLILOU,HMEROMINIA_PROSLIPSIS,MISTH OS) VALUES ('003',to_date('10-05-1998','DD-MM-YYYY'),'1200','98');
INSERT INTO IPALLILOS(ID_IPALLILOU,HMEROMINIA_PROSLIPSIS,MISTH OS) VALUES ('004',to_date('07-08-2001','DD-MM-YYYY'),'1360','198');
INSERT INTO IPALLILOS(ID_IPALLILOU,HMEROMINIA_PROSLIPSIS,MISTH OS) VALUES ('005',to_date('10-12-1994','DD-MM-YYYY'),'1400','102');
INSERT INTO PROSOPIKA_STOIXEIA(ID_IPALLILOU,ONOMA,EPONIMO,IMER OMINIA_GENNISIS)
VALUES (001,GIWRGOS,NTALARAS,TO_DATE('01-03-1965', 'DD-MM-YYYY'));
INSERT INTO PROSOPIKA_STOIXEIA(ID_IPALLILOU,ONOMA,EPONIMO,IMER OMINIA_GENNISIS)
VALUES (002,NIKOS,PAPPAS,TO_DATE('25-08-1974', 'DD-MM-YYYY'));
INSERT INTO PROSOPIKA_STOIXEIA(ID_IPALLILOU,ONOMA,EPONIMO,IMER OMINIA_GENNISIS) VALUES('003','KWSTANTINOS','PALAIKOSTAS','TO_DATE( '25-04-1975'),'DD-MM-YYYY');
INSERT INTO PROSOPIKA_STOIXEIA(ID_IPALLILOU,ONOMA,EPONIMO,IMER OMINIA_GENNISIS) VALUES('004','KSIROS','SAVVAS','TO_DATE('15-02-1875'),'DD-MM-YYYY');
INSERT INTO PROSOPIKA_STOIXEIA(ID_IPALLILOU,ONOMA,EPONIMO,IMER OMINIA_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_KER DOS)
VALUES (001,250000);
INSERT INTO PARAGWGIKOTHTA_IPALLILOU(ID_IPALLILOU,SUNOLIKO_KER DOS)
VALUES (002,410000);
INSERT INTO PARAGWGIKOTHTA_IPALLILOU(ID_IPALLILOU,SUNOLIKO_KER DOS) VALUES ('003','110000');
INSERT INTO PARAGWGIKOTHTA_IPALLILOU(ID_IPALLILOU,SUNOLIKO_KER DOS) VALUES ('004','100000');
INSERT INTO PARAGWGIKOTHTA_IPALLILOU(ID_IPALLILOU,SUNOLIKO_KER DOS) VALUES ('005','150000');
INSERT INTO KATASTASH_IPALLILOU(ID_IPALLILOU,OIKOGENIAKH_KATAS TASH,PAIDIA,POSOSTO_ANAPHRIAS)
VALUES(001, 'EGGAMOS',2,0);
INSERT INTO KATASTASH_IPALLILOU(ID_IPALLILOU,OIKOGENIAKH_KATAS TASH,PAIDIA,POSOSTO_ANAPHRIAS)
VALUES(002, 'DIAZEUGMENOS',3,20);
INSERT INTO KATASTASH_IPALLILOU(ID_IPALLILOS,OIKOGENIAKH_KATAS TASH,PAIDIA,POSOSTO_ANAPHRIAS) VALUES ('003','AGAMOS','00','0');
INSERT INTO KATASTASH_IPALLILOU(ID_IPALLILOS,OIKOGENIAKH_KATAS TASH,PAIDIA,POSOSTO_ANAPHRIAS) VALUES ('004','EGAMOS','02','0');
INSERT INTO KATASTASH_IPALLILOU(ID_IPALLILOS,OIKOGENIAKH_KATAS TASH,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.
legend-x- New Member
- Posts : 1
Reputation : 1
Language : english
Re: Help With Triggers And Funcions In Sqldeveloper oracle
This is in now way Forumotion related. So a moderator will likely (and I reckon they should) lock it. You should ask on a site that deals with SQL.
But for your first on the SQL would be:
"UPDATE table_name
SET salary=salary+200
WHERE salary < 501"
But for your first on the SQL would be:
"UPDATE table_name
SET salary=salary+200
WHERE salary < 501"
LGforum- Hyperactive
- Posts : 2265
Reputation : 264
Language : English
Location : UK
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum