Database Reference
In-Depth Information
So, let's irst create a trigger function using the following syntax:
warehouse_db=# CREATE OR REPLACE FUNCTION sales_record_insert()
RETURNS TRIGGER AS $$
BEGIN
IF (NEW.sales_date >= DATE '2014-01-01' AND
NEW.sales_date < DATE '2014-03-01') THEN
INSERT INTO sales_record_m1_to_m2 VALUES (NEW.*);
ELSEIF (NEW.sales_date >= DATE '2014-03-01' AND
NEW.sales_date < DATE '2014-05-01') THEN
INSERT INTO sales_record_m3_to_m4 VALUES (NEW.*);
ELSEIF (NEW.sales_date >= DATE '2014-05-01' AND
NEW.sales_date < DATE '2014-07-01') THEN
INSERT INTO sales_record_m5_to_m6 VALUES (NEW.*);
ELSEIF (NEW.sales_date >= DATE '2014-07-01' AND
NEW.sales_date < DATE '2014-09-01') THEN
INSERT INTO sales_record_m7_to_m8 VALUES (NEW.*);
ELSEIF (NEW.sales_date >= DATE '2014-09-01' AND
NEW.sales_date < DATE '2014-11-01') THEN
INSERT INTO sales_record_m9_to_m10 VALUES (NEW.*);
ELSEIF (NEW.sales_date >= DATE '2014-11-01' AND
NEW.sales_date < DATE '2015-01-01') THEN
INSERT INTO sales_record_m11_to_m12 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'Date is out of range. Something is wrong with
sales_record_insert_trigger() function';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
This function will simply populate the data in the respective child table on the basis
of the
sales_date
condition.
Now, the supportive trigger will call the preceding trigger function automatically
whenever a user uses the
INSERT
,
UPDATE
, or
DELETE
operations on data in the
master table.
Let's create the supportive trigger in the following manner:
warehouse_db=# CREATE TRIGGER sales_day_trigger
BEFORE INSERT ON sales_record
FOR EACH ROW
EXECUTE PROCEDURE sales_record_insert();
