Tirsdag

Jeg har brugt dagen til at læse på triggers i PostgreSQL

Triggers er en funktion der automatisk bliver invoked når der sker et bestemt event i databasen.

Create Trigger

For at lave en trigger skal man anvende CREATE FUNCTION statementet.
Herefter skal man bruge CREATE TRIGGER for at forbinde det til et table.
Et query for en function kommer til at se således ud:

 CREATE FUNCTION trigger_function() 
    RETURNS TRIGGER 
    LANGUAGE PLPGSQL
 AS $$
 BEGIN
    -- trigger logic
 END;
 $$

Som vi kan se, er der mulighed for at vælge forskellige languages der er understøttet af Postgres. Jeg vælger at tage brug af PL/pgSQL indtil videre, da det er det min tutorial følger, så det giver mening at fortsætte med det.

“$$” fungerer som single quotations, dog gør de det muligt at modtage single quotations som input uden at der sker noget.

Et query for trigger vil se således ud:

 CREATE TRIGGER trigger_name 
    {BEFORE | AFTER} { event }
    ON table_name
    [FOR [EACH] { ROW | STATEMENT }]
    EXECUTE PROCEDURE trigger_function

1) Man starter med at navngive triggeren

2) Man skal derefter vælge om triggeren skal fire FØR eller EFTER eventet sker.
3) Lige efter vi siger før eller efter, skal vi vælge hvilket EVENT der invoker triggeren. Det kan være INSERT, DELETE, UPDATE ELLER TRUNCATE.

4) Herefter vælger vi det table vi vil forbinde triggeren til.

5) Som den femte ting vi gør, skal vi vælge hvilken type trigger det skal være:

• Row-level trigger der bliver specificeret ved at sige FOR EACH ROW
• Statement-level der bliver specificeret ved at sige FOR EACH STATEMENT

En row-level trigger bliver fired for hver row, hvor en statement-level trigger bliver fired for hver transaction.

Eksempel: Vi har et table med 100 rows og to triggers der bliver fired når der sker et delete event.
Hvis DELETE statementet sletter alle 100 rows, bliver row-level triggeren fired alle 100 gange, en for hver deleted row. En statement-level trigger bliver kun fired én gang, lige meget hvor mange rows der bliver slettet.

6) Til sidst vælger man hvilken function der skal invokes når triggeren fires ved at indsætte funktionens navn efter EXECUTE PROCEDURE keywordet.

Onsdag

Jeg har igen i dag valgt at kigge på triggers og få det implementeret i projekt databasen, samt få sat foreign keys osv sat ind fra sidste uge.

Eksempel på en PostgreSQL trigger fra projekt database

Vi har vores car table:

DROP TABLE IF EXISTS car;

CREATE TABLE car(
   serialnumber CHAR(30) PRIMARY KEY,
   name CHAR(50),
   platenumber CHAR(20),
   phase CHAR(30),
   phasestart timestamp
);

Vi vil nu lave et logging table der tilhører vores car table

CREATE TABLE car_log (
id serial,
serialnumber CHAR(30) NOT NULL,
phase CHAR(30) NOT NULL,
phasestart timestamp,
changed_on timestamp NOT NULL
);

Vi laver nu en function der indsætter en ny row hvis der er sket en ændring i fasen på bilen

CREATE OR REPLACE FUNCTION log_phase_changes()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
	IF NEW.phase <> OLD.phase THEN
		 INSERT INTO car_log(serialnumber,phase,changed_on)
		 VALUES(OLD.serialnumber,OLD.phase, OLD.phasestart,now());
	END IF;

	RETURN NEW;
END;
$$

Hertil laver vi en trigger der bruger den function vi lige har lavet

CREATE TRIGGER phase_changes
  BEFORE UPDATE
  ON car
  FOR EACH ROW
  EXECUTE PROCEDURE log_phase_changes();

Vi har nu et table der indeholder alle bilers ændringer i forhold til phase, der indeholder bilens serialnumber, dens phase og phase start, og hvornår den så er blevet ændret, ergo hvornår den kom ud af den fase. Det gør det muligt for os på databasen at have overblik over hvor længe den enkelte bil har været i hver phase.

Tilføj foreign key til eksisterende table

Vi vil gerne skabe en forbindelse fra vores car table til phase og department tables. Vi skal derfor ind og alter vores car table og tilføje en FK constraint.

Vi tilføjer en constraint fra phase til car og tilføjer phaseid til car table:

ALTER TABLE car
    ADD COLUMN IF NOT EXISTS phase_id integer REFERENCES phase (phaseid);
ALTER TABLE car
    ADD CONSTRAINT fk_car_phase
    FOREIGN KEY (phase_id) 
    REFERENCES phase (phaseid);

Vi gør det samme fra department til car:

ALTER TABLE car
    ADD COLUMN IF NOT EXISTS department_id integer REFERENCES department (deparmentid);
ALTER TABLE car
    ADD CONSTRAINT fk_car_department
    FOREIGN KEY (department_id) 
    REFERENCES department(departmentid);

Vi har nu tilføjet de manglende elementer til car table og lavet en forbindelse mellem de forskellige tables.