义齿
11670 ワード
1トリガの概要
2データ可視ルールトリガ実行中、SQLコマンドを実行してトリガの親テーブルのデータにアクセスする場合、これらのSQLコマンドは次のデータ可視ルールに従います.これらのルールは、トリガの操作が変更されたテーブルのデータ行を表示できるかどうかを決定します.(1)文レベルのbeforeトリガ実行中、この文のすべてのテーブル内のデータの更新は表示されません.文レベルのafterトリガは、実行中に、その文のすべてのテーブル内のデータの更新が表示されます.
(2)行レベルbeforeフリップフロップは、実行中に同じコマンドで処理された前のすべてのデータ行が表示されるが、フリップフロップをトリガしたデータ行の更新操作の結果(挿入、更新または削除)は表示されない.行レベルafterトリガは、実行中に、同じコマンドで処理された前のすべてのデータ行が表示されます.
3事例はPL/pgSQLでフリップフロッププロセスを書くことができます.コマンドCREATE FUNCTIONでトリガプロシージャを作成できます.この関数にはパラメータがなく、戻り値のタイプはtriggerでなければなりません.コマンドCREATE TRIGGERを使用して、TG_を介してトリガを作成します.ARGVはトリガにパラメータを渡すプロセスであり、TG_について説明する.ARGVの使い方.
(1)NEW
データ型はRECORDです.行レベルトリガの場合、INSERTまたはUPDATEオペレーションによって生成された新しいデータ行が格納されます.文レベルのトリガの場合、NULLの値です.
(2)OLD
データ型はRECORDです.行レベルトリガの場合、UPDATEまたはDELETE操作によって変更または削除された古いデータ行が格納されます.文レベルのトリガの場合、NULLの値です.
(3)TG_NAME
データ型はnameで、実際に呼び出されたトリガの名前を保存します.
(4)TG_WHEN
データ型はtextであり、フリップフロップ定義情報によってはBEFOREまたはAFTERの値である.
(5)TG_LEVEL
データ型はtextであり、フリップフロップ定義情報に応じてその値はROWまたはSTATEMENTである.
(6)TG_OP
データ型はtextであり、INSERT、UPDATEまたはDELETEの値であり、トリガの動作タイプを表す.
(7)TG_RELID
データ型はoidで、トリガの役割を表すテーブルのoidです.
(8)TG_RELNAME
データ型はnameで、トリガの役割を表すテーブルの名前です.次の変数TG_TABLE_NAMEの役割は同じです.
(9)TG_TABLE_NAME
データ型はnameで、トリガの役割を表すテーブルの名前です.
(10)TG_TABLE_SCHEMA
データ型はnameで、トリガが作用するテーブルがあるパターンを表します.
(11)TG_NARGS
データ型はintegerであり、CREATE TRIGGERコマンドがフリップフロッププロセスに伝達するパラメータの個数を表す.
(12)TG_ARGV[]
データ型はtext型の配列です.CREATE TRIGGERコマンドがフリップフロップ・プロシージャに渡されるすべてのパラメータを表します.下付き文字は0から始まります.TG_ARGV[0]は最初のパラメータを表し、TG_ARGV[1]は2番目のパラメータを表し、これに類する.下付き文字が0以下またはtg以上である場合nargsは、空の値を返します.
トリガが実行中にエラーが発生したり、エラーが発生したりした場合、トリガをトリガする操作は終了します.
次に、トリガの例を示します.
(1)行レベルBEFOREトリガを使用して、テーブルempの挿入または新規操作完了後のデータ行のカラムsalary上の値が0より大きいか、カラムnameが空の値でないかを確認します.
CREATE TABLE emp (
);
CREATE FUNCTION emp_stamp() RETURNS trigger AS empstamp
empstamp LANGUAGE plpgsql;
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
(2)テーブルempに挿入、削除、および新しいデータ行を別のテーブルemp_に格納するaudit:
CREATE TABLE emp (
);
CREATE TABLE emp_audit(
);
CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS empaudit
empaudit LANGUAGE plpgsql;
CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
(3)より複雑な例であり、表sales_factはテーブルtime_を格納していますdimensionの要約データ、テーブルtime_を利用dimensionの行レベルBEFOREトリガはsales_を保持します.factとtime_dimensionでのデータ同期.
–
– Main tables - time dimension and sales fact.
–
CREATE TABLE time_dimension (
);
CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);
CREATE TABLE sales_fact (
);
CREATE INDEX sales_fact_time ON sales_fact(time_key);
–
– Summary table - sales by time.
–
CREATE TABLE sales_summary_bytime (
);
CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key);
–
– Function and trigger to amend summarized column(s) on UPDATE, INSERT, DELETE.
–
CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER AS maintsalessummarybytime
maintsalessummarybytime LANGUAGE plpgsql;
CREATE TRIGGER maint_sales_summary_bytime
AFTER INSERT OR UPDATE OR DELETE ON sales_fact
INSERT INTO sales_fact VALUES(1,1,1,10,3,15);
INSERT INTO sales_fact VALUES(1,2,1,20,5,35);
INSERT INTO sales_fact VALUES(2,2,1,40,15,135);
INSERT INTO sales_fact VALUES(2,3,1,10,1,13);
SELECT * FROM sales_summary_bytime;
DELETE FROM sales_fact WHERE product_key = 1;
SELECT * FROM sales_summary_bytime;
UPDATE sales_fact SET units_sold = units_sold * 2;
SELECT * FROM sales_summary_bytime;
(3)
ターゲット:テーブルalphasが新しいローを挿入するとtitlesのalpha_が更新されます.atがNOW()テーブルalphasが行を削除するとtitlesのalpha_が更新されるatはNULL
1、plpgsql言語をデータベースcreatelang plpgsql DATABASE 2にインストールし、triggerに戻るプロセスCREATE OR REPLACE FUNCTION afterを確立するalphas_id() RETURNS trigger AS BODY BEGIN IF( TG_OP=’DELETE’ ) THEN UPDATE titles SET alpha_at=null WHERE id=OLD.title_id; ELSE UPDATE titles SET alpha_at=NOW() WHERE id=NEW.title_id; END IF; RETURN NULL; END;
BODY LANGUAGE ‘plpgsql’; 3、トリガCREATE TRIGGER after_の作成alphas_id AFTER INSERT OR DELETE ON alphas FOR EACH ROW EXECUTE PROCEDURE after_alphas_id();
， ， ， 、 、 ， SQL 。
。 INSERT、UPDATE DELETE 。PostgreSQL ， ， 。 ， ， 。 ， 。
， 。 ， trigger。 ， CREATE TRIGGER 。 。
before after 。 before ， after 。 before ， after 。
NULL。
before ， 。 NULL, INSERT/UPDATE/DELETE 。 BEFORE ， INSERT/UPDATE/DELETE 。 UPDATE INSERT BEFORE ， ， 。
after ， NULL。
， 。 before ， 。 before NULL， ， INSERT/UPDATE/DELETE 。
before 。 after ， 。
before after ， before after ， before 。
， SQL ， ， 。 ， ， ， ， ， 。
， （ CREATE TRIGGER ）。 。
， ， （ INSERT UPDATE）， ， NEW （ INSERT UPDATE ） OLD （ UPDATE DELETE ）。 。
、 。《PL/pgSQL 》 8 。
， SET CONSTRAINTS ， 《SQL 》 CREATE CONSTRAINT TRIGGER 。
2データ可視ルールトリガ実行中、SQLコマンドを実行してトリガの親テーブルのデータにアクセスする場合、これらのSQLコマンドは次のデータ可視ルールに従います.これらのルールは、トリガの操作が変更されたテーブルのデータ行を表示できるかどうかを決定します.(1)文レベルのbeforeトリガ実行中、この文のすべてのテーブル内のデータの更新は表示されません.文レベルのafterトリガは、実行中に、その文のすべてのテーブル内のデータの更新が表示されます.
(2)行レベルbeforeフリップフロップは、実行中に同じコマンドで処理された前のすべてのデータ行が表示されるが、フリップフロップをトリガしたデータ行の更新操作の結果(挿入、更新または削除)は表示されない.行レベルafterトリガは、実行中に、同じコマンドで処理された前のすべてのデータ行が表示されます.
3事例はPL/pgSQLでフリップフロッププロセスを書くことができます.コマンドCREATE FUNCTIONでトリガプロシージャを作成できます.この関数にはパラメータがなく、戻り値のタイプはtriggerでなければなりません.コマンドCREATE TRIGGERを使用して、TG_を介してトリガを作成します.ARGVはトリガにパラメータを渡すプロセスであり、TG_について説明する.ARGVの使い方.
PL/pgSQL ， 。 ：
(1)NEW
データ型はRECORDです.行レベルトリガの場合、INSERTまたはUPDATEオペレーションによって生成された新しいデータ行が格納されます.文レベルのトリガの場合、NULLの値です.
(2)OLD
データ型はRECORDです.行レベルトリガの場合、UPDATEまたはDELETE操作によって変更または削除された古いデータ行が格納されます.文レベルのトリガの場合、NULLの値です.
(3)TG_NAME
データ型はnameで、実際に呼び出されたトリガの名前を保存します.
(4)TG_WHEN
データ型はtextであり、フリップフロップ定義情報によってはBEFOREまたはAFTERの値である.
(5)TG_LEVEL
データ型はtextであり、フリップフロップ定義情報に応じてその値はROWまたはSTATEMENTである.
(6)TG_OP
データ型はtextであり、INSERT、UPDATEまたはDELETEの値であり、トリガの動作タイプを表す.
(7)TG_RELID
データ型はoidで、トリガの役割を表すテーブルのoidです.
(8)TG_RELNAME
データ型はnameで、トリガの役割を表すテーブルの名前です.次の変数TG_TABLE_NAMEの役割は同じです.
(9)TG_TABLE_NAME
データ型はnameで、トリガの役割を表すテーブルの名前です.
(10)TG_TABLE_SCHEMA
データ型はnameで、トリガが作用するテーブルがあるパターンを表します.
(11)TG_NARGS
データ型はintegerであり、CREATE TRIGGERコマンドがフリップフロッププロセスに伝達するパラメータの個数を表す.
(12)TG_ARGV[]
データ型はtext型の配列です.CREATE TRIGGERコマンドがフリップフロップ・プロシージャに渡されるすべてのパラメータを表します.下付き文字は0から始まります.TG_ARGV[0]は最初のパラメータを表し、TG_ARGV[1]は2番目のパラメータを表し、これに類する.下付き文字が0以下またはtg以上である場合nargsは、空の値を返します.
NULL / ， 。
BEFORE ， NULL， ， INSERT/UPDATE/DELETE 。 BEFORE ， INSERT/UPDATE/DELETE 。 UPDATE INSERT BEFORE ， ， 。
AFTER ， 。
トリガが実行中にエラーが発生したり、エラーが発生したりした場合、トリガをトリガする操作は終了します.
次に、トリガの例を示します.
(1)行レベルBEFOREトリガを使用して、テーブルempの挿入または新規操作完了後のデータ行のカラムsalary上の値が0より大きいか、カラムnameが空の値でないかを確認します.
CREATE TABLE emp (
empname text,
salary integer,
last_date timestamp,
last_user text
);
CREATE FUNCTION emp_stamp() RETURNS trigger AS empstamp
BEGIN
-- Check that empname and salary are given
IF NEW.empname IS NULL THEN
RAISE EXCEPTION 'empname cannot be null';
END IF;
IF NEW.salary IS NULL THEN
RAISE EXCEPTION '% cannot have null salary', NEW.empname;
END IF;
-- Who works for us when she must pay for it?
IF NEW.salary < 0 THEN
RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
END IF;
-- Remember who changed the payroll when
NEW.last_date := current_timestamp;
NEW.last_user := current_user;
RETURN NEW;
END;
empstamp LANGUAGE plpgsql;
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
(2)テーブルempに挿入、削除、および新しいデータ行を別のテーブルemp_に格納するaudit:
CREATE TABLE emp (
empname text NOT NULL,
salary integer
);
CREATE TABLE emp_audit(
operation char(1) NOT NULL,
stamp timestamp NOT NULL,
userid text NOT NULL,
empname text NOT NULL,
salary integer
);
CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS empaudit
BEGIN
--
-- Create a row in emp_audit to reflect the operation performed on emp,
-- make use of the special variable TG_OP to work out the operation.
--
IF (TG_OP = 'DELETE') THEN
INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
RETURN NEW;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
empaudit LANGUAGE plpgsql;
CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();
(3)より複雑な例であり、表sales_factはテーブルtime_を格納していますdimensionの要約データ、テーブルtime_を利用dimensionの行レベルBEFOREトリガはsales_を保持します.factとtime_dimensionでのデータ同期.
–
– Main tables - time dimension and sales fact.
–
CREATE TABLE time_dimension (
time_key integer NOT NULL,
day_of_week integer NOT NULL,
day_of_month integer NOT NULL,
month integer NOT NULL,
quarter integer NOT NULL,
year integer NOT NULL
);
CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);
CREATE TABLE sales_fact (
time_key integer NOT NULL,
product_key integer NOT NULL,
store_key integer NOT NULL,
amount_sold numeric(12,2) NOT NULL,
units_sold integer NOT NULL,
amount_cost numeric(12,2) NOT NULL
);
CREATE INDEX sales_fact_time ON sales_fact(time_key);
–
– Summary table - sales by time.
–
CREATE TABLE sales_summary_bytime (
time_key integer NOT NULL,
amount_sold numeric(15,2) NOT NULL,
units_sold numeric(12) NOT NULL,
amount_cost numeric(15,2) NOT NULL
);
CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key);
–
– Function and trigger to amend summarized column(s) on UPDATE, INSERT, DELETE.
–
CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER AS maintsalessummarybytime
DECLARE
delta_time_key integer;
delta_amount_sold numeric(15,2);
delta_units_sold numeric(12);
delta_amount_cost numeric(15,2);
BEGIN
-- Work out the increment/decrement amount(s).
IF (TG_OP = 'DELETE') THEN
delta_time_key = OLD.time_key;
delta_amount_sold = -1 * OLD.amount_sold;
delta_units_sold = -1 * OLD.units_sold;
delta_amount_cost = -1 * OLD.amount_cost;
ELSIF (TG_OP = 'UPDATE') THEN
-- forbid updates that change the time_key -
-- (probably not too onerous, as DELETE + INSERT is how most
-- changes will be made).
IF ( OLD.time_key != NEW.time_key) THEN
RAISE EXCEPTION 'Update of time_key : % -> % not allowed', OLD.time_key, NEW.time_key;
END IF;
delta_time_key = OLD.time_key;
delta_amount_sold = NEW.amount_sold - OLD.amount_sold;
delta_units_sold = NEW.units_sold - OLD.units_sold;
delta_amount_cost = NEW.amount_cost - OLD.amount_cost;
ELSIF (TG_OP = 'INSERT') THEN
delta_time_key = NEW.time_key;
delta_amount_sold = NEW.amount_sold;
delta_units_sold = NEW.units_sold;
delta_amount_cost = NEW.amount_cost;
END IF;
-- Insert or update the summary row with the new values.
<>
LOOP
UPDATE sales_summary_bytime
SET amount_sold = amount_sold + delta_amount_sold,
units_sold = units_sold + delta_units_sold,
amount_cost = amount_cost + delta_amount_cost
WHERE time_key = delta_time_key;
EXIT insert_update WHEN found;
BEGIN
INSERT INTO sales_summary_bytime (
time_key,
amount_sold,
units_sold,
amount_cost)
VALUES (
delta_time_key,
delta_amount_sold,
delta_units_sold,
delta_amount_cost
);
EXIT insert_update;
EXCEPTION
WHEN UNIQUE_VIOLATION THEN
-- do nothing
END;
END LOOP insert_update;
RETURN NULL;
END;
maintsalessummarybytime LANGUAGE plpgsql;
CREATE TRIGGER maint_sales_summary_bytime
AFTER INSERT OR UPDATE OR DELETE ON sales_fact
FOR EACH ROW EXECUTE PROCEDURE maint_sales_summary_bytime();
INSERT INTO sales_fact VALUES(1,1,1,10,3,15);
INSERT INTO sales_fact VALUES(1,2,1,20,5,35);
INSERT INTO sales_fact VALUES(2,2,1,40,15,135);
INSERT INTO sales_fact VALUES(2,3,1,10,1,13);
SELECT * FROM sales_summary_bytime;
DELETE FROM sales_fact WHERE product_key = 1;
SELECT * FROM sales_summary_bytime;
UPDATE sales_fact SET units_sold = units_sold * 2;
SELECT * FROM sales_summary_bytime;
(3)
ターゲット:テーブルalphasが新しいローを挿入するとtitlesのalpha_が更新されます.atがNOW()テーブルalphasが行を削除するとtitlesのalpha_が更新されるatはNULL
1、plpgsql言語をデータベースcreatelang plpgsql DATABASE 2にインストールし、triggerに戻るプロセスCREATE OR REPLACE FUNCTION afterを確立するalphas_id() RETURNS trigger AS BODY BEGIN IF( TG_OP=’DELETE’ ) THEN UPDATE titles SET alpha_at=null WHERE id=OLD.title_id; ELSE UPDATE titles SET alpha_at=NOW() WHERE id=NEW.title_id; END IF; RETURN NULL; END;
BODY LANGUAGE ‘plpgsql’; 3、トリガCREATE TRIGGER after_の作成alphas_id AFTER INSERT OR DELETE ON alphas FOR EACH ROW EXECUTE PROCEDURE after_alphas_id();