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