SHARE
TWEET

Untitled

a guest Nov 15th, 2019 78 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TRIGGER Person.updateName
  2.   ON Person.Person FOR UPDATE
  3. AS
  4. SET NOCOUNT ON
  5.  
  6. DECLARE @i AS int, @num_cols AS int
  7. DECLARE @UpdCols TABLE(ordinal_position int NOT NULL PRIMARY KEY)
  8.  
  9. SET @num_cols =
  10.  (SELECT COUNT(*)
  11.   FROM INFORMATION_SCHEMA.COLUMNS
  12.   WHERE TABLE_SCHEMA = ‘Person’;
  13.     AND TABLE_NAME = ‘Person’)
  14.  
  15. SET @i = 1
  16. WHILE @i <= @num_cols
  17. BEGIN
  18.  
  19.   IF (SUBSTRING(COLUMNS_UPDATED(),(@i - 1) / 8 + 1, 1))
  20.        & POWER(2, (@i - 1) % 8) = POWER(2, (@i - 1) % 8)
  21.     INSERT INTO @UpdCols VALUES(@i)
  22.  
  23.   SET @i = @i + 1
  24.  
  25. END
  26.  
  27. SELECT COLUMN_NAME AS Updated_Column, GETDATE() AS DateModified
  28. FROM INFORMATION_SCHEMA.COLUMNS AS C JOIN @UpdCols AS U
  29.   ON C.ORDINAL_POSITION = U.ordinal_position
  30. WHERE TABLE_SCHEMA = 'Person'
  31.   AND TABLE_NAME = 'Person'
  32. ORDER BY C.ORDINAL_POSITION
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
 
Top