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