CREATE TRIGGER Exampaperdetails_AUDIT ON Exampaperdetail FOR
BEGIN UPDATE,DELETE
AS
DECLARE v_bit INT ;
v_field INT ;
v_maxfield INT ;
v_char INT ;
v_fieldname VARCHAR(128) ;
v_TableName VARCHAR(128) ;
v_PKCols VARCHAR(1000) ;
v_sql VARCHAR(2000);
v_UpdateDate VARCHAR(21) ;
v_UserName VARCHAR(128) ;
v_Type CHAR(1) ;
v_PKSelect VARCHAR(1000)
--You will need to change @TableName to match the table to be audited.
-- Here we made GUESTS for your example.
v_TableName := 'EXAMPAPERDETAIL'
-- date and user
v_UserName := SYSTEM_USER ,
v_UpdateDate := TO_CHAR (NOW(), 112)
|| ' ' || TO_CHAR (NOW(), 114)
-- Action
IF EXISTS (SELECT * FROM inserted) THEN
IF EXISTS (SELECT * FROM deleted) THEN
v_Type := 'U';
ELSE
v_Type := 'I';
END IF;
ELSE
v_Type := 'D';
END IF;
-- get list of columns
SELECT * INTO #ins FROM inserted
SELECT * INTO #del FROM deleted
-- Get primary key columns for full outer join
SELECT COALESCE(v_PKCols || ' and', ' on')
|| ' i.' || c.COLUMN_NAME || ' = d.' || c.COLUMN_NAME INTO v_PKCols
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = v_TableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
-- Get primary key select for insert
SELECT COALESCE(v_PKSelect||'+','')
|| '''<' || COLUMN_NAME
|| '=''+convert(varchar(100),
coalesce(i.' || COLUMN_NAME ||',d.' || COLUMN_NAME || '))+''>''' INTO v_PKSelect
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = v_TableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
IF v_PKCols IS NULL
THEN
RAISERROR('no PK on table %s', 16, -1, v_TableName)
RETURN;
END IF;
SELECT 0,
MAX(ORDINAL_POSITION) INTO v_field, v_maxfield
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = v_TableName
WHILE v_field < v_maxfield
LOOP
SELECT MIN(ORDINAL_POSITION) INTO v_field
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = v_TableName
AND ORDINAL_POSITION > v_field
v_bit := (v_field - 1 )% 8 + 1
v_bit := POWER(2,v_bit - 1)
v_char := ((v_field - 1) / 8) + 1
IF SUBSTRING(COLUMNS_UPDATED(),v_char, 1) & v_bit THEN >
END IF; 0
OR v_Type IN ('I','D')
BEGIN
SELECT COLUMN_NAME INTO v_fieldname
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = v_TableName
AND ORDINAL_POSITION = v_field
v_sql := '
insert LogExamPaperMarks (RegNo,
SubPaperCode,
ExamSession,
TableName,
FieldName,
OldValue,
NewValue,
Date,
UserID)
select convert(varchar(100), coalesce(i.RegNo,d.RegNo)),
convert(varchar(100), coalesce(i.SubPaperCode,d.SubPaperCode)),
convert(varchar(100), coalesce(i.ExamSession,d.ExamSession)),'''
|| v_TableName || ''','''
|| v_fieldname || ''''
|| ',convert(varchar(1000),d.' || v_fieldname || ')'
|| ',convert(varchar(1000),i.' || v_fieldname || ')'
|| ',''' || v_UpdateDate || ''''
|| ',convert(varchar(100), coalesce(i.opt1,d.opt1))'
|| ' from #ins i full outer join #del d'
|| v_PKCols
|| ' where i.' || v_fieldname || ' <> d.' || v_fieldname
|| ' or (i.' || v_fieldname || ' is null and d.'
|| v_fieldname
|| ' is not null)'
|| ' or (i.' || v_fieldname || ' is not null and d.'
|| v_fieldname
|| ' is null)'
EXECUTE (v_sql)
END
END LOOP;
ALTER TABLE dbo.Exampaperdetail ENABLE TRIGGER Exampaperdetails_AUDIT
GO
What I have tried:
Try to convert sql server trigger to postgresql trigger