09NM5E2J

1
2CREATE TRIGGER DepartmentDeleteTrigger 
3       ON HumanResources.Department
4
5AFTER DELETE
6AS
7BEGIN
8		CREATE PROCEDURE dep2Delete_SP
9		AS 
10		SELECT DepartmentID, Name, GroupName,ModifiedDate 
11		FROM HumanResources.Department
12		order by DepartmentID
13		GO
14		EXEC dep2Delete_SP
15
16			--dep2Delete_SP;
17
18       SET NOCOUNT ON;
19 
20		-- declare variables 
21        DECLARE @departID smallint
22		DECLARE @departName nvarchar(50)
23		DECLARE @departGroupNameM nvarchar(50)
24		DECLARE @departModifiedDate datetime 
25		DECLARE @departDeleteDate datetime  
26		-- set variables to data 
27		SET @departID = DepartmentID; 
28		SET @departName = Name;
29		SET @departGroupNameM = GroupName; 
30		SET @departModifiedDate = ModifiedDate; 
31		SET @departDeleteDate  = GETDATE(); 
32		-- if database exist or not 
33		IF DB_ID('DepDBackUp') is not null 	
34			PRINT 'Backup table exists'  
35		ELSE 
36			PRINT 'Backup table non-existant, creating new table ' 
37			CREATE TABLE DepDBackUp (
38				DepartmentID smallint PRIMARY KEY identity(1,1) not null, 
39				Name nvarchar(50) not null, 
40				GroupName nvarchar(50) not null, 
41			    ModifiedDate datetime not null, 
42				DeleteDate datetime not null, 
43			);
44			INSERT INTO DepDBackUp
45			values(@departID,@departName,@departGroupNameM,@departModifiedDate,@departDeleteDate) 
46
47 
48       --SELECT @CustomerId = DELETED.CustomerId       
49       --FROM DELETED
50 
51       --INSERT INTO CustomerLogs
52       --VALUES(@CustomerId, 'Deleted')
53END