edb_tables_views.htm

OIDS: tips : http://www.postgresql.org/docs/8.1/static/datatype-oid.html
Rule : http://www.postgresql.org/docs/9.1/static/rule-system.html

Object Identifier Types OIDS = False

Script Trigger

-- Trigger: emp_sal_trig on public.emp
-- DROP TRIGGER emp_sal_trig ON public.emp;
CREATE OR REPLACE TRIGGER emp_sal_trig
BEFORE INSERT OR UPDATE OR DELETE
ON public.emp
FOR EACH ROW
DECLARE
sal_diff NUMBER;
BEGIN
IF INSERTING THEN
DBMS_OUTPUT.PUT_LINE('Inserting employee ' || :NEW.empno);
DBMS_OUTPUT.PUT_LINE('..New salary: ' || :NEW.sal);
END IF;
IF UPDATING THEN
sal_diff := :NEW.sal - :OLD.sal;
DBMS_OUTPUT.PUT_LINE('Updating employee ' || :OLD.empno);
DBMS_OUTPUT.PUT_LINE('..Old salary: ' || :OLD.sal);
DBMS_OUTPUT.PUT_LINE('..New salary: ' || :NEW.sal);
DBMS_OUTPUT.PUT_LINE('..Raise : ' || sal_diff);
END IF;
IF DELETING THEN
DBMS_OUTPUT.PUT_LINE('Deleting employee ' || :OLD.empno);
DBMS_OUTPUT.PUT_LINE('..Old salary: ' || :OLD.sal);
END IF;
END;

 

-- Trigger: user_audit_trig on public.emp

-- DROP TRIGGER user_audit_trig ON public.emp;

CREATE OR REPLACE TRIGGER user_audit_trig
AFTER INSERT OR UPDATE OR DELETE
ON public.emp
FOR EACH STATEMENT
DECLARE
v_action VARCHAR2(24);
BEGIN
IF INSERTING THEN
v_action := ' added employee(s) on ';
ELSIF UPDATING THEN
v_action := ' updated employee(s) on ';
ELSIF DELETING THEN
v_action := ' deleted employee(s) on ';
END IF;
DBMS_OUTPUT.PUT_LINE('User ' || USER || v_action || TO_CHAR(SYSDATE,'YYYY-MM-DD'));
END;
 


 
 
 
public dept

10;"ACCOUNTING";"NEW YORK"
20;"RESEARCH";"DALLAS"
30;"SALES";"CHICAGO"
40;"OPERATIONS";"BOSTON"
;"";""
 
pubic emp

7369;"SMITH";"CLERK";7902;"1980-12-17 00:00:00";800.00;;20
7499;"ALLEN";"SALESMAN";7698;"1981-02-20 00:00:00";1600.00;300.00;30
7521;"WARD";"SALESMAN";7698;"1981-02-22 00:00:00";1250.00;500.00;30
7566;"JONES";"MANAGER";7839;"1981-04-02 00:00:00";2975.00;;20
7654;"MARTIN";"SALESMAN";7698;"1981-09-28 00:00:00";1250.00;1400.00;30
7698;"BLAKE";"MANAGER";7839;"1981-05-01 00:00:00";2850.00;;30
7782;"CLARK";"MANAGER";7839;"1981-06-09 00:00:00";2450.00;;10
7788;"SCOTT";"ANALYST";7566;"1987-04-19 00:00:00";3000.00;;20
7839;"KING";"PRESIDENT";;"1981-11-17 00:00:00";5000.00;;10
7844;"TURNER";"SALESMAN";7698;"1981-09-08 00:00:00";1500.00;0.00;30
7876;"ADAMS";"CLERK";7788;"1987-05-23 00:00:00";1100.00;;20
7900;"JAMES";"CLERK";7698;"1981-12-03 00:00:00";950.00;;30
7902;"FORD";"ANALYST";7566;"1981-12-03 00:00:00";3000.00;;20
7934;"MILLER";"CLERK";7782;"1982-01-23 00:00:00";1300.00;;10
7936;"Mathew";"clerk";7902;"1987-10-28 00:00:00";1300.00;;20
;"";"";;"";;;
 

 
job list

7369;"1980-12-17 00:00:00";"";"CLERK";800.00;;20;"New Hire"
7499;"1981-02-20 00:00:00";"";"SALESMAN";1600.00;300.00;30;"New Hire"
7521;"1981-02-22 00:00:00";"";"SALESMAN";1250.00;500.00;30;"New Hire"
7566;"1981-04-02 00:00:00";"";"MANAGER";2975.00;;20;"New Hire"
7654;"1981-09-28 00:00:00";"";"SALESMAN";1250.00;1400.00;30;"New Hire"
7698;"1981-05-01 00:00:00";"";"MANAGER";2850.00;;30;"New Hire"
7782;"1981-06-09 00:00:00";"";"MANAGER";2450.00;;10;"New Hire"
7788;"1987-04-19 00:00:00";"1988-04-12 00:00:00";"CLERK";1000.00;;20;"New Hire"
7788;"1988-04-13 00:00:00";"1989-05-04 00:00:00";"CLERK";1040.00;;20;"Raise"
7788;"1990-05-05 00:00:00";"";"ANALYST";3000.00;;20;"Promoted to Analyst"
7839;"1981-11-17 00:00:00";"";"PRESIDENT";5000.00;;10;"New Hire"
7844;"1981-09-08 00:00:00";"";"SALESMAN";1500.00;0.00;30;"New Hire"
7876;"1987-05-23 00:00:00";"";"CLERK";1100.00;;20;"New Hire"
7900;"1981-12-03 00:00:00";"1983-01-14 00:00:00";"CLERK";950.00;;10;"New Hire"
7900;"1983-01-15 00:00:00";"";"CLERK";950.00;;30;"Changed to Dept 30"
7902;"1981-12-03 00:00:00";"";"ANALYST";3000.00;;20;"New Hire"
7934;"1982-01-23 00:00:00";"";"CLERK";1300.00;;10;"New Hire"
;"";"";"";;;;""
 
Script: to create the tables:

Manas 14pc -postgreSQL Entrise DB
dept /emp /joblist


-- Table: public.dept

-- DROP TABLE public.dept;

CREATE TABLE public.dept
(
deptno numeric(2,0) NOT NULL,
dname character varying(14),
loc character varying(13),
CONSTRAINT dept_pk PRIMARY KEY (deptno),
CONSTRAINT dept_dname_uq UNIQUE (dname)
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.dept
OWNER TO enterprisedb;
GRANT ALL ON TABLE public.dept TO enterprisedb;
GRANT ALL ON TABLE public.dept TO public;

-- Table: public.emp

-- DROP TABLE public.emp;

CREATE TABLE public.emp
(
empno numeric(4,0) NOT NULL,
ename character varying(10),
job character varying(9),
mgr numeric(4,0),
hiredate timestamp without time zone,
sal numeric(7,2),
comm numeric(7,2),
deptno numeric(2,0),
CONSTRAINT emp_pk PRIMARY KEY (empno),
CONSTRAINT emp_ref_dept_fk FOREIGN KEY (deptno)
REFERENCES public.dept (deptno) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT emp_sal_ck CHECK (sal > 0::numeric)
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.emp
OWNER TO enterprisedb;
GRANT ALL ON TABLE public.emp TO enterprisedb;
GRANT ALL ON TABLE public.emp TO public;

-- Trigger: emp_sal_trig on public.emp

-- DROP TRIGGER emp_sal_trig ON public.emp;

CREATE OR REPLACE TRIGGER emp_sal_trig
BEFORE INSERT OR UPDATE OR DELETE
ON public.emp
FOR EACH ROW
DECLARE
sal_diff NUMBER;
BEGIN
IF INSERTING THEN
DBMS_OUTPUT.PUT_LINE('Inserting employee ' || :NEW.empno);
DBMS_OUTPUT.PUT_LINE('..New salary: ' || :NEW.sal);
END IF;
IF UPDATING THEN
sal_diff := :NEW.sal - :OLD.sal;
DBMS_OUTPUT.PUT_LINE('Updating employee ' || :OLD.empno);
DBMS_OUTPUT.PUT_LINE('..Old salary: ' || :OLD.sal);
DBMS_OUTPUT.PUT_LINE('..New salary: ' || :NEW.sal);
DBMS_OUTPUT.PUT_LINE('..Raise : ' || sal_diff);
END IF;
IF DELETING THEN
DBMS_OUTPUT.PUT_LINE('Deleting employee ' || :OLD.empno);
DBMS_OUTPUT.PUT_LINE('..Old salary: ' || :OLD.sal);
END IF;
END;

-- Trigger: user_audit_trig on public.emp

-- DROP TRIGGER user_audit_trig ON public.emp;

CREATE OR REPLACE TRIGGER user_audit_trig
AFTER INSERT OR UPDATE OR DELETE
ON public.emp
FOR EACH STATEMENT
DECLARE
v_action VARCHAR2(24);
BEGIN
IF INSERTING THEN
v_action := ' added employee(s) on ';
ELSIF UPDATING THEN
v_action := ' updated employee(s) on ';
ELSIF DELETING THEN
v_action := ' deleted employee(s) on ';
END IF;
DBMS_OUTPUT.PUT_LINE('User ' || USER || v_action || TO_CHAR(SYSDATE,'YYYY-MM-DD'));
END;


-- Table: public.jobhist

-- DROP TABLE public.jobhist;

CREATE TABLE public.jobhist
(
empno numeric(4,0) NOT NULL,
startdate timestamp without time zone NOT NULL,
enddate timestamp without time zone,
job character varying(9),
sal numeric(7,2),
comm numeric(7,2),
deptno numeric(2,0),
chgdesc character varying(80),
CONSTRAINT jobhist_pk PRIMARY KEY (empno, startdate),
CONSTRAINT jobhist_ref_dept_fk FOREIGN KEY (deptno)
REFERENCES public.dept (deptno) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE SET NULL,
CONSTRAINT jobhist_ref_emp_fk FOREIGN KEY (empno)
REFERENCES public.emp (empno) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE,
CONSTRAINT jobhist_date_chk CHECK (startdate <= enddate)
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.jobhist
OWNER TO enterprisedb;
GRANT ALL ON TABLE public.jobhist TO enterprisedb;
GRANT ALL ON TABLE public.jobhist TO public;
 
 
Views :

Script:

-- View: public.salesemp

-- DROP VIEW public.salesemp;

CREATE OR REPLACE VIEW public.salesemp AS
SELECT emp.empno, emp.ename, emp.hiredate, emp.sal, emp.comm
FROM emp
WHERE emp.job::text = 'SALESMAN'::text;

ALTER TABLE public.salesemp
OWNER TO enterprisedb;
GRANT ALL ON TABLE public.salesemp TO enterprisedb;
GRANT ALL ON TABLE public.salesemp TO public;


-- Rule: salesemp_d ON public.salesemp

-- DROP RULE salesemp_d ON public.salesemp;

CREATE OR REPLACE RULE salesemp_d AS
ON DELETE TO salesemp DO INSTEAD DELETE FROM emp
WHERE emp.empno = old.empno;

-- Rule: salesemp_i ON public.salesemp

-- DROP RULE salesemp_i ON public.salesemp;

CREATE OR REPLACE RULE salesemp_i AS
ON INSERT TO salesemp DO INSTEAD INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (new.empno, new.ename, 'SALESMAN'::character varying, 7698, new.hiredate, new.sal, new.comm, 30);

-- Rule: salesemp_u ON public.salesemp

-- DROP RULE salesemp_u ON public.salesemp;

CREATE OR REPLACE RULE salesemp_u AS
ON UPDATE TO salesemp DO INSTEAD UPDATE emp SET empno = new.empno, ename = new.ename, hiredate = new.hiredate, sal = new.sal, comm = new.comm
WHERE emp.empno = old.empno;