Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • World
  • Users
  • Groups
Skins
  • Light
  • Cerulean
  • Cosmo
  • Flatly
  • Journal
  • Litera
  • Lumen
  • Lux
  • Materia
  • Minty
  • Morph
  • Pulse
  • Sandstone
  • Simplex
  • Sketchy
  • Spacelab
  • United
  • Yeti
  • Zephyr
  • Dark
  • Cyborg
  • Darkly
  • Quartz
  • Slate
  • Solar
  • Superhero
  • Vapor

  • Default (No Skin)
  • No Skin
Collapse
Code Project
  1. Home
  2. Database & SysAdmin
  3. Database
  4. SQL Trigger to get updated cell

SQL Trigger to get updated cell

Scheduled Pinned Locked Moved Database
databasehelpquestionannouncement
2 Posts 2 Posters 0 Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • I Offline
    I Offline
    Indra PR
    wrote on last edited by
    #1

    I just got some SQL Query to get updated cell like below:

    CREATE TRIGGER LaurenQuantrell
    ON Lauren AFTER UPDATE
    AS
    IF UPDATE(KeyCol)
    BEGIN
    RAISERROR ('Dont change the key column!!', 16, 1)
    ROLLBACK TRANSACTION
    RETURN
    END

    INSERT Quantrell (KeyCol, ColName, NewData)
    SELECT inserted.KeyCol, 'DataCol1', inserted.DataCol1
    FROM inserted
    INNER JOIN deleted ON deleted.KeyCol = inserted.KeyCol
    WHERE inserted.DataCol1 <> deleted.DataCol1

    INSERT Quantrell (KeyCol, ColName, NewData)
    SELECT inserted.KeyCol, 'DataCol2', inserted.DataCol1
    FROM inserted
    INNER JOIN deleted ON deleted.KeyCol = inserted.KeyCol
    WHERE inserted.DataCol2 <> deleted.DataCol2

    INSERT Quantrell (KeyCol, ColName, NewData)
    SELECT inserted.KeyCol, 'DataCol3', inserted.DataCol3
    FROM inserted
    INNER JOIN deleted ON deleted.KeyCol = inserted.KeyCol
    WHERE inserted.DataCol3 <> deleted.DataCol3

    And, I want to change it into dynamic SQL since it is too much to type the column one by one:

    DECLARE @COLUMN_NUMBER AS integer
    DECLARE @COLUMN_TOTAL AS integer
    DECLARE @COLUMN_NAME AS nvarchar(100)
    DECLARE @SQL AS nvarchar(1024)

    SET @COLUMN_NUMBER = 1
    SET @COLUMN_TOTAL = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'transaksi_request')

    WHILE @COLUMN_NUMBER <= @COLUMN_TOTAL
    BEGIN
    SET @COLUMN_NAME = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'transaksi_request' AND ORDINAL_POSITION = @COLUMN_NUMBER)
    SET @SQL =
    'INSERT transaksi_requestlog(log_date, log_user, log_id, log_column)
    SELECT ''' + CAST(GETDATE() AS nvarchar(20)) + ''', Inserted.request_modifiedby, Inserted.request_id, ''' + @COLUMN_NAME + '''
    FROM Inserted
    INNER JOIN Deleted ON Deleted.request_id' + ' = Inserted.request_id' + '
    WHERE Inserted.' + @COLUMN_NAME + ' <> Deleted.' + @COLUMN_NAME
    EXEC(@SQL)

    SET @COLUMN_NUMBER = @COLUMN_NUMBER + 1
    END

    And it got an error message saying Invalid object name 'Inserted' Can anyone help me? Thanks

    - No Signature Available -

    W 1 Reply Last reply
    0
    • I Indra PR

      I just got some SQL Query to get updated cell like below:

      CREATE TRIGGER LaurenQuantrell
      ON Lauren AFTER UPDATE
      AS
      IF UPDATE(KeyCol)
      BEGIN
      RAISERROR ('Dont change the key column!!', 16, 1)
      ROLLBACK TRANSACTION
      RETURN
      END

      INSERT Quantrell (KeyCol, ColName, NewData)
      SELECT inserted.KeyCol, 'DataCol1', inserted.DataCol1
      FROM inserted
      INNER JOIN deleted ON deleted.KeyCol = inserted.KeyCol
      WHERE inserted.DataCol1 <> deleted.DataCol1

      INSERT Quantrell (KeyCol, ColName, NewData)
      SELECT inserted.KeyCol, 'DataCol2', inserted.DataCol1
      FROM inserted
      INNER JOIN deleted ON deleted.KeyCol = inserted.KeyCol
      WHERE inserted.DataCol2 <> deleted.DataCol2

      INSERT Quantrell (KeyCol, ColName, NewData)
      SELECT inserted.KeyCol, 'DataCol3', inserted.DataCol3
      FROM inserted
      INNER JOIN deleted ON deleted.KeyCol = inserted.KeyCol
      WHERE inserted.DataCol3 <> deleted.DataCol3

      And, I want to change it into dynamic SQL since it is too much to type the column one by one:

      DECLARE @COLUMN_NUMBER AS integer
      DECLARE @COLUMN_TOTAL AS integer
      DECLARE @COLUMN_NAME AS nvarchar(100)
      DECLARE @SQL AS nvarchar(1024)

      SET @COLUMN_NUMBER = 1
      SET @COLUMN_TOTAL = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'transaksi_request')

      WHILE @COLUMN_NUMBER <= @COLUMN_TOTAL
      BEGIN
      SET @COLUMN_NAME = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'transaksi_request' AND ORDINAL_POSITION = @COLUMN_NUMBER)
      SET @SQL =
      'INSERT transaksi_requestlog(log_date, log_user, log_id, log_column)
      SELECT ''' + CAST(GETDATE() AS nvarchar(20)) + ''', Inserted.request_modifiedby, Inserted.request_id, ''' + @COLUMN_NAME + '''
      FROM Inserted
      INNER JOIN Deleted ON Deleted.request_id' + ' = Inserted.request_id' + '
      WHERE Inserted.' + @COLUMN_NAME + ' <> Deleted.' + @COLUMN_NAME
      EXEC(@SQL)

      SET @COLUMN_NUMBER = @COLUMN_NUMBER + 1
      END

      And it got an error message saying Invalid object name 'Inserted' Can anyone help me? Thanks

      - No Signature Available -

      W Offline
      W Offline
      Wendelius
      wrote on last edited by
      #2

      AFAIK you cannot use inserted or deleted tables via dynamic sql in a trigger. Perhaps you could use COLUMNS_UPDATED or UPDATE functions instead.

      The need to optimize rises from a bad design. My articles[^]

      1 Reply Last reply
      0
      Reply
      • Reply as topic
      Log in to reply
      • Oldest to Newest
      • Newest to Oldest
      • Most Votes


      • Login

      • Don't have an account? Register

      • Login or register to search.
      • First post
        Last post
      0
      • Categories
      • Recent
      • Tags
      • Popular
      • World
      • Users
      • Groups