create sequence SEQ_XT_LOG minvalue 1 maxvalue 9999999999999999999999999999 start with 1062 increment by 1 cache 20 cycle; 下面的触发器的作用是:任何时候表中插入或更新了行, 当前的用户名和时间都记录入行中. 并且它保证给出了雇员名称并且薪水是一个正数. CREATE TABLE emp ( empname text, salary integer, last_date timestamp, last_user text );CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS ' BEGIN -- 检查是否给出了 empname 和 salary IF NEW.empname ISNULL THEN RAISE EXCEPTION ''empname cannot be NULL value''; END IF; IF NEW.salary ISNULL THEN RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname; END IF; -- 我们必须付帐给谁? IF NEW.salary < 0 THEN RAISE EXCEPTION ''% cannot have a negative salary'', NEW.empname; END IF; -- 记住何时何人的薪水被修改了 NEW.last_date := ''now''; NEW.last_user := current_user; RETURN NEW; END; ' LANGUAGE 'plpgsql';CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
minvalue 1
maxvalue 9999999999999999999999999999
start with 1062
increment by 1
cache 20
cycle;
下面的触发器的作用是:任何时候表中插入或更新了行, 当前的用户名和时间都记录入行中. 并且它保证给出了雇员名称并且薪水是一个正数. CREATE TABLE emp (
empname text,
salary integer,
last_date timestamp,
last_user text
);CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS '
BEGIN
-- 检查是否给出了 empname 和 salary
IF NEW.empname ISNULL THEN
RAISE EXCEPTION ''empname cannot be NULL value'';
END IF;
IF NEW.salary ISNULL THEN
RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname;
END IF; -- 我们必须付帐给谁?
IF NEW.salary < 0 THEN
RAISE EXCEPTION ''% cannot have a negative salary'', NEW.empname;
END IF; -- 记住何时何人的薪水被修改了
NEW.last_date := ''now'';
NEW.last_user := current_user;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();