SHARE
TWEET

Untitled

a guest Jul 17th, 2019 74 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. USE [Northwind_GAH];
  2. GO
  3.  
  4. DROP TABLE [dbo].[ChangeLog];
  5. GO
  6.  
  7. CREATE TABLE [dbo].[ChangeLog]
  8. (
  9. [ChangeID]  INT             IDENTITY(1,1) PRIMARY KEY,
  10. [EmpID]     INT             NULL,
  11. [User]      NVARCHAR(30)    NOT NULL,
  12. [Date]      SMALLDATETIME   NOT NULL,
  13. [OldRate]   MONEY           NULL,
  14. [NewRate]   MONEY           NOT NULL
  15. );
  16. GO
  17.  
  18. ALTER TABLE [dbo].[Employees] ADD
  19. [PayRate] MONEY NOT NULL DEFAULT 8.00;
  20. GO
  21.  
  22. CREATE TRIGGER payChange ON Employees FOR UPDATE
  23. AS
  24.     IF UPDATE (PayRate)
  25.     BEGIN
  26.         DECLARE @emp_id     INT,
  27.                 @user       NVARCHAR(30),
  28.                 @date       SMALLDATETIME,
  29.                 @oldrate    MONEY,
  30.                 @newrate    MONEY
  31.  
  32.         SELECT @emp_id = inserted.EmployeeID
  33.         FROM inserted
  34.         SELECT @user = SUSER_SNAME()
  35.         SELECT @date = GETDATE()
  36.         SELECT @oldrate = deleted.PayRate
  37.         FROM deleted
  38.         SELECT @newrate = inserted.PayRate
  39.         FROM inserted
  40.  
  41.         INSERT INTO ChangeLog(EmpID, [User], [Date], [OldRate], [NewRate])
  42.         VALUES (@emp_id, @user, @date, @oldrate, @newrate)
  43.  
  44.     END;
  45. GO
  46.  
  47. DROP TRIGGER payChange
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top