iDEqY2w2

1--CREATING THE SP 
2GO 
3CREATE PROCEDURE dep2Delete_SP
4AS 
5SELECT DepartmentID, Name, GroupName,ModifiedDate 
6FROM HumanResources.Department
7order by DepartmentID
8go 
9-- CREATING A TRIGGER ON HUMAN RESOURCE DEPARTMENT 
10CREATE TRIGGER DepartmentDeleteTrigger 
11       ON HumanResources.Department
12AFTER DELETE
13AS
14begin  
15
16		SET NOCOUNT ON;
17
18		-- EXECUTING THE SP 
19		EXEC dep2Delete_SP
20		
21
22		-- if database exist or not 
23
24		IF DB_ID('DepDBackUp') is not null 	
25			PRINT 'Backup table exists'  
26		ELSE 
27			PRINT 'Backup table non-existant, creating new table ' 
28
29			-- DECLARING VARIABLES 
30        DECLARE @departID smallint,
31 		 @departName nvarchar(50),
32		 @departGroupNameM nvarchar(50),
33		 @departModifiedDate datetime ,
34		 @departDeleteDate datetime  
35		
36
37		-- SETTING VARIABLES TO DELETED DEPARTMENT VARIABLES 
38		SELECT @departID = DELETED.DepartmentID,  @departName = DELETED.Name,
39		 @departGroupNameM = DELETED.GroupName, @departModifiedDate = DELETED.ModifiedDate 
40       FROM DELETED
41
42		SET @departDeleteDate  = GETDATE(); 
43
44		-- Creating the backup database table 
45			CREATE TABLE DepDBackUp (
46				DepartmentID smallint PRIMARY KEY identity(1,1) not null, 
47				Name nvarchar(50) not null, 
48				GroupName nvarchar(50) not null, 
49			    ModifiedDate datetime not null, 
50				DeleteDate datetime not null, 
51			);
52			-- inserting it 
53			INSERT INTO DepDBackUp
54			values(@departID,@departName,@departGroupNameM,@departModifiedDate,@departDeleteDate) 
55			-- done 
56end