CODEWITHSUNDEEP
sqlsql-serverdatabase
Avishai Yaniv
Avishai Yaniv

Reputation: 410

TRIGGER DELETE with two conditions

These are the relevant tables for this question:

Employee (EID, FirstName, LastName) - father
ConstructorEmployee (EID, CompanyName, SalaryPerDay) - son
Project (PID)
ProjectsConstructorEmployees (EID, PID, ...)

I would like to create a trigger that whenever someone deletes a project, it will delete all constructor employees from the employee table (and with cascade, the matching constructor employee), that is working only in the deleted project.

That means that if employee 1 works at projects 1 and 2, employee 2 works only at project 2, after deleting project 2, employee 2 will be deleted while 1 will not.

This is what I've tried so far but I'm missing the only one project part.

------- 1 -------
CREATE TRIGGER DeleteProject
AFTER DELETE 
ON Project 
AS
    DELETE FROM Employee
    WHERE E.EID == (SELECT EID 
                    FROM ConstructorEmployee as CE
                    WHERE CE.PID == (SELECT PID FROM deleted)) 
GO

Thanks in advance.

Upvotes: 1

Views: 132

Answers (2)

Mani Kandan
Mani Kandan

Reputation: 107

HOPE THIS HELPS.

   CREATE TABLE Employee(EID INT,FirstName varchar(500),LastName varchar(50))

GO

   CREATE TABLE ConstructorEmployee(EID INT,CompanyName varchar(500),SalaryPerDay bigint)

GO

   CREATE TABLE Project(PID INT)

GO

  CREATE TABLE ProjectsConstructorEmployees(EID int ,PID INT)

GO

   INSERT INTO Employee
   SELECT 1,'MANI','KANDAN'
   UNION
   SELECT 2,'RAM','CHARAN'
   UNION
   SELECT 3,'RAGU','NATH'

GO INSERT INTO ConstructorEmployee SELECT 1,'HST',20 UNION SELECT 2,'RRR',50 UNION SELECT 3,'FRT',90

GO

  INSERT INTO PROJECT 
  SELECT 9
  UNION
  SELECT 10  
  UNION
  SELECT 13

GO INSERT INTO ProjectsConstructorEmployees SELECT 1,9 UNION SELECT 1,10 UNION SELECT 2,13 UNION SELECT 3,9 UNION SELECT 3,10

GO

 SELECT * FROM Employee
 SELECT * FROM ConstructorEmployee
 SELECT * FROM Project
 SELECT * FROM ProjectsConstructorEmployees

GO

 CREATE TRIGGER PROJ_DEL
 ON Project
 FOR  DELETE
 AS
 DECLARE @A TABLE (A INT)
 INSERT INTO @A 
 SELECT * FROM DELETED
 DELETE S FROM ConstructorEmployee S WHERE EID IN(
 SELECT D.EID FROM ProjectsConstructorEmployees D JOIN (
 SELECT A.* FROM ProjectsConstructorEmployees A INNER JOIN @A  B ON
 ( A.PID=B.A) INNER JOIN ConstructorEmployee C ON (A.EID = C.EID))F ON 
 (D.EID=F.EID)
 GROUP BY D.EID 
 HAVING COUNT(D.EID)=1)

GO

  DELETE FROM PROJECT WHERE PID=10---EID 10 MAPPED TO MORE THAN ONE PROJ SO 
  IT WILL NOT ALLOW FOR DELETE

  DELETE FROM PROJECT WHERE PID=13---EID 13 MAPPED TO  ONLY ONE PROJ SO IT 
  WILL  ALLOW FOR DELETE

Upvotes: -1

GMB
GMB

Reputation: 222442

You need to wrap your delete in a BEGIN/END block.

In your trigger, you can use the IN comparison operator to include a query that returns the list of the employee ids to delete.

To build the id list, you want to lookup all employees that did work on that project in table ProjectsConstructorEmployees, and then filter out all those who are currently recorded on another project, using a correlated query and a NOT EXIST clause.

CREATE TRIGGER DeleteProject
AFTER DELETE ON Project AS
BEGIN
    DELETE
    FROM Employee
    WHERE E.EID IN (
        SELECT PCE.EID 
        FROM 
            DELETED DEL
            INNER JOIN ProjectsConstructorEmployees PCE ON PCE.PID = DEL.PID
        WHERE NOT EXISTS (
            SELECT 1 
            FROM ProjectsConstructorEmployees
            WHERE EID  = PCE.EID
            AND   PID != DEL.PID
        )
    )
END

NB : for your logic to make sense, you must add another step after this one, to unconditionnaly delete all projects-related records from table ProjectsConstructorEmployees. Else employee that ever worked on two different projects will never go away.

DELETE FROM ProjectsConstructorEmployees WHERE PID = (SELECT PID FROM DELETED)

Upvotes: 2

Related Questions