Use Atlantic;
GO
IF (OBJECT_ID(N'[dbo].[trgAfterDelete]') IS NOT NULL)
BEGIN
      DROP TRIGGER [dbo].trgAfterDelete;
END;
GO
CREATE TRIGGER trgAfterDelete ON [dbo].[AtlanticEmployees] 
FOR DELETE
AS
	DECLARE @empid int;
	DECLARE @emp_Old_Name VARCHAR(100);
	DECLARE @emp_New_Name VARCHAR(100);
	DECLARE @emp_Old_Sal DECIMAL(10,2);
	DECLARE @emp_New_Sal DECIMAL(10,2);
	DECLARE @audit_action VARCHAR(100);
	DECLARE @HostName VARCHAR(100);
	DECLARE @UserName VARCHAR(100);

	SELECT @empid=i.emp_ID FROM deleted AS i;	
	SELECT @emp_Old_Name=i.emp_Name FROM deleted i;	
	SELECT @emp_Old_Sal=i.emp_Sal FROM deleted i;	
	SET @audit_action='Deleted Row -- After Delete Trigger.';
	SET @emp_New_Name = 'Deleted Employee'
	SET @HostName = HOST_NAME()
	SET @UserName = SUSER_NAME()
	--SET @emp_New_Sal = 0
	INSERT INTO dbo.AtlanticEmployeesAudit
           (emp_ID, emp_Old_Name, emp_New_Name, emp_Old_Sal, emp_New_Sal, 
				 HostName, UserName, audit_Action, audit_Timestamp) 
	VALUES (@empid, @emp_Old_Name, @emp_New_Name, @emp_Old_Sal, @emp_New_Sal, 
			 @HostName, @UserName, @audit_action, GETDATE());

	PRINT 'AFTER DELETE trigger fired.'
GO



-- DROP the trigger if sequential INSERT INTO statements are executed from this query

