Frequently Asked Questions

The following procedure can be called by any INSERT,DELETE,UPDATE trigger as shown after it.

CREATE PROCEDURE sisfm_db_update_logger
  @tableName VARCHAR(50),
  @columnsUpdated VARBINARY(MAX)
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @UserID VARCHAR(50) = UPPER(CONVERT(VARCHAR(50), SESSION_CONTEXT(N'UserID')));

  IF @UserID IS NULL RETURN;

  DECLARE @PK VARCHAR(8000) = '';
  DECLARE curPK CURSOR LOCAL FOR
  SELECT RTRIM(field_name) FROM sisfm_flds WHERE table_name=@tableName AND primary_key>0 ORDER BY primary_key;
  OPEN curPK;
  WHILE 1=1
  BEGIN
    DECLARE @fieldName VARCHAR(50);
    FETCH NEXT FROM curPK INTO @fieldName; IF @@FETCH_STATUS <> 0 BREAK;
    IF @PK <> '' SET @PK = @PK + '+''~''+';
    SET @PK = @PK + 'RTRIM(CONVERT(VARCHAR(50), i.' + @fieldName + '))';
  END
  CLOSE curPK;

  IF @PK = '' RETURN;

  DECLARE @query NVARCHAR(4000);

  IF EXISTS(SELECT * FROM #tempDeleted) AND EXISTS(SELECT * FROM #tempInserted)
    BEGIN
      DECLARE curColumnUpdated CURSOR LOCAL FOR
      SELECT name
      FROM syscolumns
      WHERE id = OBJECT_ID(@tableName)
      AND CONVERT(VARBINARY,REVERSE(@columnsUpdated)) & POWER(CONVERT(BIGINT, 2), colorder - 1) > 0;
      OPEN curColumnUpdated;
      WHILE 1=1
      BEGIN
        DECLARE @columnUpdated VARCHAR(50);
        FETCH NEXT FROM curColumnUpdated INTO @columnUpdated; IF @@FETCH_STATUS <> 0 BREAK;

        SET @query = 'SELECT GETDATE(),''' + @UserID + ''',''' + @tableName + ''',' + @PK + ',''UPDATE'','
        + '''' + @columnUpdated + ''','
        + 'd.' + @columnUpdated + ','
        + 'i.' + @columnUpdated
        + ' FROM #tempInserted i'
        + ' INNER JOIN #tempDeleted d ON ' + @PK + '=' + REPLACE(@PK,'i.','d.')
        + ' WHERE d.' + @columnUpdated + '!=i.'+@columnUpdated
        + ' OR d.' + @columnUpdated + ' IS NULL AND i.'+@columnUpdated + ' IS NOT NULL'
        + ' OR i.' + @columnUpdated + ' IS NULL AND d.'+@columnUpdated + ' IS NOT NULL';

        INSERT INTO sisfm_db_update_log (date_time, user_name, table_name, primary_key, update_action , field_name, field_value_old, field_value_new)
        EXEC sp_executesql @query;
      END;
      CLOSE curColumnUpdated;
    END
  ELSE
    IF EXISTS(SELECT * FROM #tempDeleted)
      BEGIN
        SET @query = 'SELECT GETDATE(),''' + @UserID + ''',''' + @tableName + ''',' + @PK + ',''DELETE'' FROM #tempDeleted i';

        INSERT INTO sisfm_db_update_log (date_time, user_name, table_name, primary_key, update_action )
        EXEC sp_executesql @query;
      END
    ELSE
      BEGIN
        SET @query = 'SELECT GETDATE(),''' + @UserID + ''',''' + @tableName + ''',' + @PK + ',''INSERT'' FROM #tempInserted i';

        INSERT INTO sisfm_db_update_log (date_time, user_name, table_name, primary_key, update_action )
        EXEC sp_executesql @query;
      END

The following creates a trigger for table rm which calls this stored procedure:

CREATE TRIGGER TR_rm
  ON rm
  AFTER INSERT,DELETE,UPDATE
AS
BEGIN
  SELECT * INTO #tempInserted FROM INSERTED;
  SELECT * INTO #tempDeleted FROM DELETED;

  DECLARE @tableName VARCHAR(50); SELECT @tableName = OBJECT_NAME(parent_obj) FROM SYSOBJECTS WHERE id=@@PROCID;
  DECLARE @columnsUpdated VARBINARY(MAX) = COLUMNS_UPDATED();

  EXEC sisfm_db_update_logger @tableName, @columnsUpdated

  DROP TABLE #tempInserted;
  DROP TABLE #tempDeleted;
END

The following procedure is called by SISfm before each INSERT,DELETE,UPDATE to store the SISfm user name:

CREATE OR REPLACE PROCEDURE sisfm_set_key
(
  key IN VARCHAR2,
  val IN VARCHAR2
) AS
BEGIN
  DBMS_SESSION.SET_CONTEXT('sisfm', key, val);
END;

The following function can be called by a trigger to get the SISfm user name:

CREATE OR REPLACE FUNCTION sisfm_get_key
(
  key IN VARCHAR2
)
RETURN VARCHAR2
AS
BEGIN
  DECLARE
    val VARCHAR2(50);
  BEGIN
    SELECT SYS_CONTEXT('sisfm', key) INTO val FROM dual;
    RETURN val;
  END;
END;

The following procedure can be called by a trigger to log a field change:

CREATE OR REPLACE PROCEDURE sisfm_db_update_logger
(
  v_UserID IN VARCHAR2,
  v_tableName IN VARCHAR2,
  v_PKValue IN VARCHAR2,
  v_fieldName IN VARCHAR2,
  v_fieldValueOld IN VARCHAR2,
  v_fieldValueNew IN VARCHAR2
)
AS
  v_updateAction VARCHAR2(6);
BEGIN
  IF (v_fieldValueNew IS NULL AND v_fieldValueOld IS NOT NULL)
  OR (v_fieldValueOld IS NULL AND v_fieldValueNew IS NOT NULL)
  OR v_fieldValueNew <> v_fieldValueOld THEN

    v_updateAction := 'UPDATE';
    IF DELETING THEN v_updateAction := 'DELETE'; END IF;
    IF INSERTING THEN v_updateAction := 'INSERT'; END IF;

    INSERT INTO sisfm_db_update_log (date_time, user_name, table_name, primary_key, update_action , field_name, field_value_old, field_value_new)
    VALUES (SYSDATE, v_UserID, v_tableName, v_PKValue, v_updateAction, v_fieldName, v_fieldValueOld, v_fieldValueNew);
  END IF;
END;

The following creates a trigger for table rm field name:

CREATE OR REPLACE TRIGGER TR_rm
BEFORE DELETE OR UPDATE OR INSERT ON rm
FOR EACH ROW
DECLARE
  v_UserID VARCHAR2(50);
  v_PKValue VARCHAR2(999);
BEGIN
  v_UserID := sisfm_get_key('UserID'); IF v_UserID IS NULL THEN RETURN; END IF;

  IF INSERTING THEN
    EXECUTE IMMEDIATE ('SELECT ''' || RTRIM(:new.BL_ID) || '~' || RTRIM(:new.FL_ID) || ''' FROM dual') INTO v_PKValue;
  ELSE
    EXECUTE IMMEDIATE ('SELECT ''' || RTRIM(:old.BL_ID) || '~' || RTRIM(:old.FL_ID) || ''' FROM dual') INTO v_PKValue;
  END IF;

  -- for each column to be logged:

  sisfm_db_update_logger(v_UserID, 'FL', v_PKValue, 'NAME', :old.NAME, :new.NAME);
END;