Re: Exporting modifications to a table as XML/JSON - Mailing list pgsql-sql
| From | David G. Johnston |
|---|---|
| Subject | Re: Exporting modifications to a table as XML/JSON |
| Date | |
| Msg-id | CAKFQuwZpF=gyowZq+2MdwAytkm_m1NuqCJQ6fG-dwtpYfg_a7w@mail.gmail.com Whole thread Raw |
| In response to | Exporting modifications to a table as XML/JSON (<ertan.kucukoglu@1nar.com.tr>) |
| Responses |
Re: Exporting modifications to a table as XML/JSON
|
| List | pgsql-sql |
On Mon, May 11, 2020 at 2:17 PM <ertan.kucukoglu@1nar.com.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=@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.