6.1. Requêtes SQL

6.1.1. Before Starting

Before proceeding to actions described further, SQL-queries for this step should be performed.

6.1.2. Creating a Trigger Function that Sets the Status "New" when Opening a Ticket

CREATE OR REPLACE FUNCTION trig_t_objects_i_new_status()

RETURNS trigger AS

$BODY$

DECLARE

var_status_id integer := 1;          -- ID of the "New" status;

BEGIN

INSERT INTO t_statuses (userid, object_id, status_type_id)

VALUES (NEW.userid, NEW.object_id, var_status_id);

RETURN NEW;

END;

$BODY$

LANGUAGE plpgsql VOLATILE;

6.1.3. Creating a Trigger Function that Updates the Current Status of the Ticket when Adding a New Status 

CREATE OR REPLACE FUNCTION trig_t_statuses_iu_status()

RETURNS trigger AS

$BODY$

BEGIN

UPDATE t_objects 

SET object_cur_status_id = NEW.status_type_id

WHERE t_objects.object_id = NEW.object_id;

RETURN NEW;

END;

$BODY$

LANGUAGE plpgsql VOLATILE;

6.1.4. Creating a Trigger for the Table t_objects

CREATE TRIGGER trig_t_objects_i_new_status

AFTER INSERT

ON t_objects

FOR EACH ROW

EXECUTE PROCEDURE trig_t_objects_i_new_status();

6.1.5. Creating a Trigger for the Table t_statuses 

CREATE TRIGGER trig_t_statuses_iu_status

AFTER INSERT

ON t_statuses

FOR EACH ROW

EXECUTE PROCEDURE trig_t_statuses_iu_status();

6.1.6. Next

6.2. Creating Compound Table 1002 Tickets