Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --Zadanie 5
- GO
- CREATE TRIGGER wynagrodzenie
- ON employees
- FOR UPDATE
- AS
- BEGIN
- DECLARE @wynagrodzenie money, @minSalary money, @maxSalary money, @id VARCHAR, @pracownik INT
- SET @id = (SELECT inserted.job_id FROM inserted)
- SET @pracownik = (SELECT inserted.employee_id FROM inserted)
- SET @wynagrodzenie = (SELECT inserted.salary FROM inserted)
- SET @maxSalary = (SELECT jobs.max_salary FROM jobs WHERE jobs.job_id = @id)
- SET @minSalary = (SELECT jobs.min_salary FROM jobs WHERE jobs.job_id = @id)
- IF (@wynagrodzenie > @maxSalary)
- BEGIN
- UPDATE employees SET salary = @maxSalary WHERE employee_id = @pracownik
- PRINT('Zmodyfikowano wynagrodzenie do wartości maksymalnej: ' + CAST(@maxSalary as VARCHAR) + ' dla pracownika ' + CAST(@pracownik as VARCHAR))
- END
- IF (@wynagrodzenie < @minSalary)
- BEGIN
- UPDATE employees SET salary = @minSalary WHERE employee_id = @pracownik
- PRINT('Zmodyfikowano wynagrodzenie do wartości minimalnej: ' + CAST(@minSalary as VARCHAR) + ' dla pracownika ' + CAST(@pracownik as VARCHAR))
- END
- END
- GO
- SELECT * FROM employees WHERE employee_id = 103
- SELECT * FROM jobs WHERE job_id = 'IT_PROG'
- UPDATE employees SET salary = 50000 WHERE employee_id = 103
- GO
- DROP TRIGGER wynagrodzenie
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement