Tom Kelliher, CS 318
Feb. 4, 2002
Read 5.1--4.
The E/R model.
ON Event Action
CREATE TABLE Teaching ( ProfId INTEGER, CrsCode CHAR(6), Semester CHAR(6), PRIMARY KEY (CrsCode, Semester), FOREIGN KEY (ProfId) REFERENCES Professor ON DELETE NO ACTION ON UPDATE CASCADE, FOREIGN KEY (CrsCode) REFERENCES Course ON DELETE SET NULL ON UPDATE CASCADE );
CREATE TRIGGER
specifies an event an associates code ---
a stored procedure --- with it.
CREATE FUNCTION RaiseCheck () RETURNS OPAQUE AS ' BEGIN IF NEW.Salary > 1.05 * OLD.Salary THEN -- Excessive salary raise - abort transaction. RAISE EXCEPTION ''% given an excessive raise'', OLD.EmpName; END IF; -- Salary increase OK, proceed with transaction. RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER LimitRaises BEFORE UPDATE ON Employee FOR EACH ROW EXECUTE PROCEDURE RaiseCheck();
NEW.Salary := 1.05 * OLD.Salary;
.
Examples:
DROP TABLE AllMyLifesWork; -- Whoops. DROP DATABASE kelliher; ALTER USER kelliher WITH PASSWORD 'BitterSweetMint'; ALTER TABLE People ADD COLUMN FavIceCream CHAR(6); ALTER TABLE People ADD FOREIGN KEY (FavIceCream) REFERENCES IceCream (Code);PostgreSQL limited on alter functionality. Copy table adding/deleting appropriately.
GRANT SELECT, UPDATE ON Employee TO GROUP Personnel; GRANT SELECT ON Books TO PUBLIC; GRANT ALL ON BillG TO kelliher;The object of a grant can be a table, view, or sequence (PostgreSQL).
CREATE VIEW ProfStud AS SELECT Te.ProfId AS Prof, Tr.StuID AS Stud FROM Transcript Tr, Teaching Te WHERE Tr.CrsCode = Te.CrsCode AND Tr.Semester = Te.Semester;PostgreSQL version of 4.5.