how to Audit updates to table data using a table function that modifies SQL data

Vikash
Posted by Vikash under Sql Server category on | Points: 40 | Views : 1349
This function updates the salary of an employee specified by "updEmpNum", 4by the amount specified by "amount", and also records in an audit table named 4"audit_table", the user that invoked the routine, the name of the table 4that was modified, and the type of modification made by the user. A 4SELECT statement that references an UPDATE statement in the FROM clause is 4used to get back the updated row values.

CREATE FUNCTION update_salary(updEmpNum CHAR(4), amount INTEGER)
RETURNS TABLE (emp_lastname VARCHAR(10),
emp_firstname VARCHAR(10),
newSalary INTEGER)
LANGUAGE SQL
MODIFIES SQL DATA
NO EXTERNAL ACTION
NOT DETERMINISTIC
BEGIN ATOMIC
INSERT INTO audit_table(user, table, action, time)
VALUES (USER,
'EMPLOYEE',
'Update of employee salary. Values: '
|| updEmpNum || ' ' || char(amount),
CURRENT_TIMESTAMP);
RETURN
SELECT lastname, firstname, salary
FROM FINAL TABLE(UPDATE employee
SET salary = salary + amount
WHERE employee.empnum = updEmpNum);
END

Comments or Responses

Posted by: T.Saravanan on: 9/19/2012 Level:Silver | Status: [Member] [MVP] | Points: 10
Please post your code inside the code tag.
Posted by: Vikash on: 9/21/2012 Level:Starter | Status: [Member] | Points: 10
Ok sir,

thank you for your suggestion...

Login to post response