uR99Recd

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 @departID smallint
21		DECLARE @departName nvarchar(50)
22		DECLARE @departGroupNameM nvarchar(50)
23		DECLARE @departModifiedDate datetime 
24		DECLARE @departDeleteDate datetime  
25
26		-- if database exist or not 
27		IF DB_ID('DepDBackUp') is not null 	
28			PRINT 'Backup table exists'  
29		ELSE 
30			PRINT 'Backup table non-existant, creating new table ' 
31			CREATE TABLE DepDBackUp (
32				DepartmentID smallint PRIMARY KEY identity(1,1) not null, 
33				Name nvarchar(50) not null, 
34				GroupName nvarchar(50) not null, 
35			    ModifiedDate datetime not null, 
36				DeleteDate datetime not null, 
37			);
38			INSERT INTO DepDBackUp
39			values(@departID,@departName,@departGroupNameM,@departModifiedDate,@departDeleteDate) 
40
41 
42       --SELECT @CustomerId = DELETED.CustomerId       
43       --FROM DELETED
44 
45       --INSERT INTO CustomerLogs
46       --VALUES(@CustomerId, 'Deleted')
47END