Exporting modifications to a table as XML/JSON

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Exporting modifications to a table as XML/JSON

Ertan Küçükoğlu
Hello,

I am using PostgreSQL 12.2.

Below is an SQL Server trigger I am provided and asked to convert to
PostgreSQL. I do not have access to SQL Server database nor I am provided
additional details. This is more or less dead end, but I am taking my
chances, just in case.

I briefly know PostgreSQL and using it. Unfortunately, my knowledge is not
in that detail to create a trigger. I did not have the need to do that so
far. My main use case is simple CRUD operations.

I have tried to read several different resources, I also found that script
example https://github.com/2ndQuadrant/audit-trigger to log executed
modifying SQL statements in a separate scheme. However, it did not help me
much as I am asked in verbal to log old values and new values as XML/JSON of
my choice (example script is XML).

In the end, I even failed to understand if this is possible to achieve with
PostgreSQL at all.

CREATE TRIGGER dbo.Users_Trigger ON dbo.Users
AFTER INSERT, UPDATE, DELETE
AS
  if context_info()= 0x4321 return
  SET NOCOUNT ON

-------------- Audit Controls -----------------
  DECLARE @RecID int,@RefCode varchar(10),@FldsUpdated varchar(max), @SqlStr
nvarchar(max),@OldValues xml,@NewValues xml

  SELECT @FldsUpdated = COALESCE(@FldsUpdated+',','')+COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS Field
  WHERE TABLE_NAME = 'Users' and
  (sys.fn_IsBitSetInBitmask(COLUMNS_UPDATED(),
COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME,
'ColumnID')) <> 0 or not
  Exists(Select * From inserted) )
  and not COLUMN_NAME in ('Photo','PassE')
 
  Select * Into #TempTbl From
  (Select State=0,* From Deleted
   Union All
   Select State=1,* From Inserted
  ) X

  Declare Audit_Cursor CURSOR LOCAL FAST_FORWARD FOR
  SELECT distinct RecId,Code
  FROM #TempTbl
 
  Open Audit_Cursor
  Fetch Next FROM Audit_Cursor INTO @RecID,@RefCode
  While @@FETCH_STATUS = 0
  Begin
    if Exists(Select * from #TempTbl Where RecID=@RecID)
    begin
      ---Old Values
      Set @SqlStr=N'Select @XmlValOut=(Select '+@FldsUpdated+' From #TempTbl
Root Where RecID=@RecIDIn and State=0 For Xml Auto)';
      Exec sp_ExecuteSql @SqlStr, N'@RecIDIn int, @XmlValOut xml
OUTPUT',@RecIDIn=@RecID,@XmlValOut=@OldValues OUTPUT
      ---New Values
      Set @SqlStr=N'Select @XmlValOut=(Select '+@FldsUpdated+' From #TempTbl
Root Where RecID=@RecIDIn and State=1 For Xml Auto)';
      Exec sp_ExecuteSql @SqlStr, N'@RecIDIn int,@XmlValOut xml
OUTPUT',@RecIDIn=@RecID,@XmlValOut=@NewValues OUTPUT
 
      Exec usp_SaveAudit @RecID,@RefCode,0,'','','Users',@OldValues,
@NewValues
    end
 
    Fetch Next FROM Audit_Cursor INTO @RecID,@RefCode
  end --while--
  Close Audit_Cursor
  DeAllocate Audit_Cursor
  Drop Table #TempTbl
------- Audit Controls ---------------
  SET NOCOUNT OFF

I do not know what usp_SaveAudit() procedure does, tough it is likely saving
XML into a table/disk file.

Any help is appreciated.

Thanks & regards,
Ertan



Reply | Threaded
Open this post in threaded view
|

Re: Exporting modifications to a table as XML/JSON

David G Johnston
On Mon, May 11, 2020 at 2:17 PM <[hidden email]> wrote:

Seemingly this trigger has been attached to the "Users" table AND the two columns "Photo" and "PassE" should not be audited.  Probably this is also filtering out columns where the before and after values (for an UPDATE) are the same so only actual changed values are recorded.

  SELECT @FldsUpdated = COALESCE(@FldsUpdated+',','')+COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS Field
  WHERE TABLE_NAME = 'Users' and
  (sys.fn_IsBitSetInBitmask(COLUMNS_UPDATED(),
COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME,
'ColumnID')) <> 0 or not
  Exists(Select * From inserted) )
  and not COLUMN_NAME in ('Photo','PassE')


What is the end result of the record - present (1) or absent (0), along with the content of those records

  Select * Into #TempTbl From
  (Select State=0,* From Deleted
   Union All
   Select State=1,* From Inserted
  ) X

  Declare Audit_Cursor CURSOR LOCAL FAST_FORWARD FOR
  SELECT distinct RecId,Code
  FROM #TempTbl

For each recordId, also tack on the "code" - it seems to have meaning in the saveAudit function


  Open Audit_Cursor
  Fetch Next FROM Audit_Cursor INTO @RecID,@RefCode
  While @@FETCH_STATUS = 0

This seems paranoid, just got the recordID from the table

  Begin
    if Exists(Select * from #TempTbl Where RecID=@RecID)

Generate dynamic SQL to include a call to functions that convert records to xml (suggest json for PostgreSQL)
Guessing here but for an update it seems like a record is added to both "Deleted" and "Inserted"

    begin

Deleted records have old values but not new ones (state = 0)
 
      ---Old Values
      Set @SqlStr=N'Select @XmlValOut=(Select '+@FldsUpdated+' From #TempTbl
Root Where RecID=@RecIDIn and State=0 For Xml Auto)';
      Exec sp_ExecuteSql @SqlStr, N'@RecIDIn int, @XmlValOut xml
OUTPUT',@RecIDIn=@RecID,@XmlValOut=@OldValues OUTPUT

Inserted records have new values but not old ones (state = 1)

      ---New Values
      Set @SqlStr=N'Select @XmlValOut=(Select '+@FldsUpdated+' From #TempTbl
Root Where RecID=@RecIDIn and State=1 For Xml Auto)';
      Exec sp_ExecuteSql @SqlStr, N'@RecIDIn int,@XmlValOut xml
OUTPUT',@RecIDIn=@RecID,@XmlValOut=@NewValues OUTPUT

SaveAudit takes the old and new content and saves it somewhere - local file on the server is an option, a rights-restricted table in the current database is the easiest, a table in a separate database works pretty well, and other options exist.
 
      Exec usp_SaveAudit @RecID,@RefCode,0,'','','Users',@OldValues,
@NewValues
    end


Clean up
    Fetch Next FROM Audit_Cursor INTO @RecID,@RefCode
  end --while--
  Close Audit_Cursor
  DeAllocate Audit_Cursor
  Drop Table #TempTbl
------- Audit Controls ---------------
  SET NOCOUNT OFF

I do not know what usp_SaveAudit() procedure does, tough it is likely saving
XML into a table/disk file.

If you get to choose just put the data into a table in the current database that only a superuser can read.

In short, yes, this or something similar can be built in PostgreSQL.  Depending how close to the original you need to stay the easiest solution is to ignore the "only changed values on update" requirement and just capture everything before and after - aside from any fields that must be excluded.  You could just hard-code the column list in the trigger function - this is very simple (and you probably start here regardless) but has the main downside of not adapting should new columns be added to the table.

I am sure that examples exist in the wild of attaching an audit trigger to a table so that the json form of the OLD and NEW (the PostgreSQL version of "Inserted" and "Deleted" (more or less?) records can be either directly inserted into another table or passed to a function that handles that task (simple functional encapsulation).

Not going to say its easy - and I've never actually implemented one just read up on them a bit - but it can be done.  I would suggest that you forget the technical syntax for a bit and using pseudo-code and some examples describe precisely what you need to accomplish.  Then you should burn the code shown above and figure out how to implement your requirements in PostgreSQL from first principles - not by trying to convert code written for the old system.  Close examples written for PostgreSQL will be much better than an exact-but-incomplete example from a different system.

HTH

David J.

Reply | Threaded
Open this post in threaded view
|

Re: Exporting modifications to a table as XML/JSON

David G Johnston
On Mon, May 11, 2020 at 4:37 PM David G. Johnston <[hidden email]> wrote:
I am sure that examples exist in the wild of attaching an audit trigger to a table so that the json form of the OLD and NEW (the PostgreSQL version of "Inserted" and "Deleted" (more or less?) records can be either directly inserted into another table or passed to a function that handles that task (simple functional encapsulation).

In particular you should review the documentation:


Both the "record" oriented one and the newer "transition tables" one - which is probably more similar to what your example code does (haven't used that feature myself).

David J.