CODEWITHSUNDEEP
sqlsql-serverdatabasetriggers
António Pedro Leite
António Pedro Leite

Reputation: 41

SQL TRIGGERS DATABASE

I have to implement a trigger in a table (Employee), the trigger should "trigger" when a simple employee gets a salary higher than the manager of his department. And i have done this.

CREATE TRIGGER sal on EMPLOYEE

AFTER INSERT,UPDATE
as
declare @sal int;
declare @sal_mgr int;
declare @sal_manager TABLE(sal int)

SELECT @sal = salary FROM EMPLOYEE

SELECT @sal_mgr = salary FROM EMPLOYEE join Department on ssn = mgr_ssn where dno=dnumber

INSERT INTO @sal_manager values (@sal_mgr)

UPDATE EMPLOYEE SET salary = @sal_mgr-1 where salary >= @sal_mgr

But with this i only get the salary of 1 manager, not all i have in the different departments (its only allowed 1 manager for each department)

Upvotes: 0

Views: 112

Answers (1)

Shadow
Shadow

Reputation: 34284

There are 2 major problems with your code:

  1. As I mentioned in a comment, you have multiple sql commands within the trigger (apparently), but there is no begin ... end structure to mark the start and end of the statements belonging to the trigger. Your code does not comply with mysql's create trigger syntax either.

  2. None of the sql queries you included are run for the updated / inserted record, they run on the entire table. This explain why the code compares everything with the same manager: the last manager in the resultset of the query.

The inserted / updated record's fields can be accessed using the NEW keyword (see examples in mysql documentation)

SELECT @sal = salary FROM EMPLOYEE; -> not required, it is simply NEW.salary

SELECT @sal_mgr = salary FROM EMPLOYEE join Department on ssn = mgr_ssn where dno=dnumber;

The above should simply be (made a few assumptions which fields belong to which table because it is not clear from your code):

SELECT @sal_mgr = salary
FROM EMPLOYEE
inner join Department on EMPLOYEE.ssn = Department.mgr_ssn
where Department.dno=NEW.dnumber

You can rewrite all other queries based on the logic demonstrated above + pls use the proper mysql syntax for creating a trigger and using begin - end block.

Upvotes: 1

Related Questions