Re: Exporting modifications to a table as XML/JSON

On Mon, May 11, 2020 at 2:17 PM <ertan(dot)kucukoglu(at)1nar(dot)com(dot)tr> 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=(at)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 '+(at)FldsUpdated+' From
> #TempTbl
> Root Where RecID=(at)RecIDIn and State=0 For Xml Auto)';
> Exec sp_ExecuteSql @SqlStr, N'@RecIDIn int, @XmlValOut xml
> OUTPUT',@RecIDIn=(at)RecID,@XmlValOut=(at)OldValues OUTPUT
>

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

---New Values
> Set @SqlStr=N'Select @XmlValOut=(Select '+(at)FldsUpdated+' From
> #TempTbl
> Root Where RecID=(at)RecIDIn and State=1 For Xml Auto)';
> Exec sp_ExecuteSql @SqlStr, N'@RecIDIn int,@XmlValOut xml
> OUTPUT',@RecIDIn=(at)RecID,@XmlValOut=(at)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.