ehH1V5vm

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