-- upgrade an installation from 3.2.4 to 3.2.5
-- Krzysztof Kowalczyk (krzysztofk@pobox.com)
--

-- 
-- sdm.sql
--

drop function trig_baf_audit();

create function trig_baf_audit() returns opaque
as '
DECLARE
	the_name	varchar(200);
BEGIN
	select first_names || '' '' || last_name into the_name
	from users where user_id= NEW.last_updated_by;

	IF OLD.baf_type != NEW.baf_type
	THEN 
	insert into baf_audit (baf_id, who, what, old_value, new_value, audit_date)
	values (NEW.baf_id, the_name, ''baf_type'', OLD.baf_type, NEW.baf_type, sysdate());
	END IF;

	IF OLD.baf_status != NEW.baf_status
	THEN 
	insert into baf_audit (baf_id, who, what, old_value, new_value, audit_date)
	values (NEW.baf_id, the_name, ''baf_status'', sdm_get_baf_status(OLD.baf_status), sdm_get_baf_status(NEW.baf_status), sysdate());
	END IF;

	IF OLD.severity != NEW.severity
	THEN 
	insert into baf_audit (baf_id, who, what, old_value, new_value, audit_date)
	values (NEW.baf_id, the_name, ''severity'', OLD.severity, NEW.severity, sysdate());
	END IF;

	IF OLD.expected_completion != NEW.expected_completion
	THEN 
	insert into baf_audit (baf_id, who, what, old_value, new_value, audit_date)
	values (NEW.baf_id, the_name, ''expected_completion'', fetch_release_name(OLD.expected_completion), fetch_release_name(NEW.expected_completion), sysdate());
	END IF;

	return NEW;
END;
' language 'plpgsql';

drop trigger baf_audit on bugs_and_features;

create trigger baf_audit
after update on bugs_and_features
for each row
execute procedure trig_baf_audit();