Help With Triggers And Funcions In Sql - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Help With Triggers And Funcions In Sql

legend-xlegend-x Posts: 2Member
I have created the following tables and the following inserts:

create table employee
(
id_user number primary key,
hire_date date,
salary float,
bonus float
)


create table personal
(
id_user number primary key,
fname char(30),
lname char(30),
birth_date date
)


create table prod_per_year
(
id_user number primary key,
profit float,
years int
)


create table total_product
(
id_user number primary key,
total_profit float
)

create table status
(
id_user number primary key,
family_status char(40),
children int,
disability_rate float
)


insert into employee values (001,'12-03-2006',2000,350)
insert into employee values (002,'21-03-2006',1700,200)
insert into employee values (003,'22-02-2007',2400,650)
insert into employee values (004,'10-08-2008',2100,450)
insert into employee values (005,'12-03-2007',1900,400)

insert into personal values (001,'Dimitris','Ksaplopoulos','05-04-1982')
insert into personal values (002,'Giannis','Koimisis','20-02-1984')
insert into personal values (003,'Iasonas','Tempelhanidis','23-12-1986')
insert into personal values (004,'Triantafyllos','Kourasmenakis','14-08-1979')
insert into personal values (005,'Manolis','Aplas','02-05-1984')

insert into prod_per_year values (001,450,4)
insert into prod_per_year values (002,300,4)
insert into prod_per_year values (003,500,4)
insert into prod_per_year values (004,730,4)
insert into prod_per_year values (005,550,4)



insert into status values(001, 'normal',2,0)
insert into status values(002, 'normal',2,0)
insert into status values(003, 'normal',3,0)
insert into status values(004,'divorced',2,0)
insert into status values(005, 'normal',1,10)


insert into total_product values(001,3500)
insert into total_product values(002,2500)
insert into total_product values(003,3700)
insert into total_product values(004,4100)
insert into total_product values(005,3000)


i have some trouble of doing the 1. 2. 3. 4. if anyone can help i would appreciate it

1. we have to create a funcion in which if in the table employee 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 prod_per_year for each employee make sure to refresh the table total_product automatically using trigger (trigger based).

thanks in advance for the help.
Sign In or Register to comment.