Update a row(s) in Table B for each Deleted Row in Table A

Question

Update a row(s) in Table B for each Deleted Row in Table A

smiF

Say Hey Kid

Points: 664
More actions
April 29, 2016 at 8:04 pm

#313552

I plan to use a After Delete Trigger.
/* CREATE TABLE */CREATE TABLE EMP(empidempnamedeptid VARCHAR(100));/* INSERT QUERY */INSERT INTO EMP(empidempnamedeptid) VALUES( '333Ali653');
/* INSERT QUERY */INSERT INTO EMP(empidempnamedeptid) VALUES( '243Joe23455');
/* INSERT QUERY */INSERT INTO EMP(empidempnamedeptid) VALUES( '222Steve78');
/* INSERT QUERY */INSERT INTO EMP(empidempnamedeptid) VALUES( '433Markj75');
/* INSERT QUERY */INSERT INTO EMP(empidempnamedeptid) VALUES( '322Dale45');
/* INSERT QUERY */INSERT INTO EMP(empidempnamedeptid) VALUES( '432Stephanie70');
/* INSERT QUERY */INSERT INTO EMP(empidempnamedeptid) VALUES( '777Kent745');
/* INSERT QUERY */INSERT INTO EMP(empidempnamedeptid) VALUES( '122Julius25');
/* INSERT QUERY */INSERT INTO EMP(empidempnamedeptid) VALUES( '233Tovarus97');
/* INSERT QUERY */INSERT INTO EMP(empidempnamedeptid) VALUES( '111Flint6');
/* INSERT QUERY */INSERT INTO EMP(empidempnamedeptid) VALUES( '133Mosbacker332');
/* INSERT QUERY */INSERT INTO EMP(empidempnamedeptid) VALUES( '2121Davidsion1333');
/* INSERT QUERY */INSERT INTO EMP(empidempnamedeptid) VALUES( '2221SHEGOZIY1776');
/* CREATE TABLE */CREATE TABLE EMPHISTORY(EmpIDDeptIDIsActive VARCHAR(100));/* INSERT QUERY */INSERT INTO EMPHISTORY(EmpIDDeptIDIsActive) VALUES( '3336531');
/* INSERT QUERY */INSERT INTO EMPHISTORY(EmpIDDeptIDIsActive) VALUES( '2432321');
/* INSERT QUERY */INSERT INTO EMPHISTORY(EmpIDDeptIDIsActive) VALUES( '222781');
/* INSERT QUERY */INSERT INTO EMPHISTORY(EmpIDDeptIDIsActive) VALUES( '433751');
/* INSERT QUERY */INSERT INTO EMPHISTORY(EmpIDDeptIDIsActive) VALUES( '322451');
/* INSERT QUERY */INSERT INTO EMPHISTORY(EmpIDDeptIDIsActive) VALUES( '432701');
/* INSERT QUERY */INSERT INTO EMPHISTORY(EmpIDDeptIDIsActive) VALUES( '7777451');
/* INSERT QUERY */INSERT INTO EMPHISTORY(EmpIDDeptIDIsActive) VALUES( '122251');
/* INSERT QUERY */INSERT INTO EMPHISTORY(EmpIDDeptIDIsActive) VALUES( '233971');
/* INSERT QUERY */INSERT INTO EMPHISTORY(EmpIDDeptIDIsActive) VALUES( '11161');
/* INSERT QUERY */INSERT INTO EMPHISTORY(EmpIDDeptIDIsActive) VALUES( '1333321');
/* INSERT QUERY */INSERT INTO EMPHISTORY(EmpIDDeptIDIsActive) VALUES( '212113331');
/* INSERT QUERY */INSERT INTO EMPHISTORY(EmpIDDeptIDIsActive) VALUES( '222117761');
Here is what I have done so far.: I thought it would suffice but It only update one record leaving all the others without
the 0 set in is active.
CREATE TRIGGER trg_mrk_inactive
ON [dbo].[emp]
AFTER DELETE
AS
BEGIN
DECLARE @EMPID INT
DECLARE @DEPTID INT
SELECT @EMPID = empid FROM deleted --@EMPID = DELETED EMPID
SELECT @DEPTID = empid FROM deleted --@DEPTID = DELETED DEPTID
UPDATE [dbo].[Emphistory]
SET IsActive = 0
WHERE EmpID = @EMPID OR [DeptID] = @DEPTID
END

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63428 More actions · Answer 1

Welcome to SSC. Thanks for the CREATE TABLE and INSERT scripts... they're easier to use if you post them this way, though:

CREATE TABLE Employee(

EmployeeID INT PRIMARY KEY,

EmployeeName VARCHAR(20) NOT NULL,

DeptID INT);

GO

INSERT INTO Employee VALUES(333,'Ali',653);

INSERT INTO Employee VALUES(243,'Joe',23455);

INSERT INTO Employee VALUES(222,'Steve',78);

INSERT INTO Employee VALUES(433,'Markj',75);

INSERT INTO Employee VALUES(322,'Dale',45);

INSERT INTO Employee VALUES(432,'Stephanie',70);

INSERT INTO Employee VALUES(777,'Kent',745);

INSERT INTO Employee VALUES(122,'Julius',25);

INSERT INTO Employee VALUES(233,'Tovarus',97);

INSERT INTO Employee VALUES(111,'Flint',6);

INSERT INTO Employee VALUES(133,'Mosbacker',332);

INSERT INTO Employee VALUES(2121,'Davidsion',1333);

INSERT INTO Employee VALUES(2221,'SHEGOZIY',1776);

CREATE TABLE EmployeeHISTORY(EmployeeIDINT

,DeptIDINT

,IsActive BIT);

GO

INSERT INTO EmployeeHISTORY VALUES(333,653, 1);

INSERT INTO EmployeeHISTORY VALUES(243,232,1);

INSERT INTO EmployeeHISTORY VALUES(222,78,1);

INSERT INTO EmployeeHISTORY VALUES(433,75,1);

INSERT INTO EmployeeHISTORY VALUES(322,45,1);

INSERT INTO EmployeeHISTORY VALUES(432,70,1);

INSERT INTO EmployeeHISTORY VALUES(777,745,1);

INSERT INTO EmployeeHISTORY VALUES(122,25,1);

INSERT INTO EmployeeHISTORY VALUES(233,97,1);

INSERT INTO EmployeeHISTORY VALUES(111,6,1);

INSERT INTO EmployeeHISTORY VALUES(133,332,1);

INSERT INTO EmployeeHISTORY VALUES(2121,1333,1);

INSERT INTO EmployeeHISTORY VALUES(2221,1776,1);

If you always want to delete the EmployeeHistory records of an Employee when the Employee is deleted, you can just create a foreign key constraint on the EmployeeHistory table and use ON DELETE CASCADE.

CREATE TABLE EmployeeHistory

(

EmployeeIDINT

,DeptIDINT

,IsActive BIT)

CONSTRAINT fk_EmployeeID

FOREIGN KEY (EmployeeID)

REFERENCES Employee (EmployeeID)

ON DELETE CASCADE

);

Then you don't have to use a trigger.

smiF Say Hey Kid Points: 664 More actions · Answer 2

How would I be able to similarly do this as a trigger.

pietlinden SSC Guru Points: 63428 More actions · Answer 3

This is a great discussion on triggers.

Here's the MS page on creating triggers. https://msdn.microsoft.com/en-us/library/ms189799.aspx

Edit - just looked around here and Dwain Camps wrote a great article on triggers[/url]. Definitely worth a read.

smiF Say Hey Kid Points: 664 More actions · Answer 4

Greetings Guys I decided to go ahead and post the solution I came up with.

ALTER TRIGGER trg_mrk_inactive
ON [dbo].[emp]
AFTER DELETE --CREATES TRIGGER AFTER DELETE ACTION
AS
BEGIN
INSERT INTO Emphistory ---WILL INSERT INTO EMPHISTORY THE DELETED VALUES SPECIFIED IN THE SELECT ADDING - ISACTIVE = 0
SELECT D.empid, D.DEPTID, ISACTIVE = 0
FROM DELETED AS D