フリップフロップ同期更新{{フリップフロップ:どうきこうしん}}
1710 ワード
MySQL環境の下で、関連するトリガを創立して、2つの表の間でsqlに関連して、みんなを助けることができることを望みます.personテーブルの作成
CREATE TABLE
建person_ex
CREATE TABLE
2.実現の目標personが新たに1つの記録を追加し、1つの記録を更新するか、または1つの記録を削除する場合、person_exのsexフィールドがMのレコードは、sqlコードを変更します.挿入トリガを作成します.
create trigger person_trigger AFTER insert on person for each row begin set @x = "hello trigger"; IF new.sex = 'M' THEN INSERT INTO person_ex(id,name,sex) VALUES (New.id,new.
更新トリガの作成:
create trigger person_trigger_update AFTER UPDATE on person for each row begin set @x = "trigger UPDATE"; IF new.sex = 'M' THEN DELETE from person_ex where person_ex.id = new.id; INSERT into person_ex SELECT * from person where person.id = new.id; END IF; end;
削除トリガの作成:
create trigger person_trigger_delete AFTER DELETE on person for each row begin set @x = "trigger DELETE"; DELETE FROM person_ex where person_ex.id = old.id; end;
3.SQLコードのテスト:
INSERT INTO person (id,NAME,sex) VALUES (1,'chigo','M') ; INSERT INTO person (id,NAME,sex) VALUES (2,'Tony','F') ;
UPDATE person SET NAME = 'vara' , sex = 'F' WHERE id = 1 ; UPDATE person SET NAME = 'Tony' , sex = 'M' WHERE id = 1 ; UPDATE person SET NAME = 'vara' , sex = 'M' WHERE id = 1 ;
DELETE FROM person WHERE id = 1 ;
CREATE TABLE
person
( id
int(11) DEFAULT NULL, name
varchar(256) DEFAULT NULL, sex
char(16) DEFAULT NULL ) 建person_ex
CREATE TABLE
person_ex
( id
int(11) DEFAULT NULL, name
varchar(256) DEFAULT NULL, sex
char(16) DEFAULT NULL ) 2.実現の目標personが新たに1つの記録を追加し、1つの記録を更新するか、または1つの記録を削除する場合、person_exのsexフィールドがMのレコードは、sqlコードを変更します.挿入トリガを作成します.
create trigger person_trigger AFTER insert on person for each row begin set @x = "hello trigger"; IF new.sex = 'M' THEN INSERT INTO person_ex(id,name,sex) VALUES (New.id,new.
name
,new.sex); END IF; end; 更新トリガの作成:
create trigger person_trigger_update AFTER UPDATE on person for each row begin set @x = "trigger UPDATE"; IF new.sex = 'M' THEN DELETE from person_ex where person_ex.id = new.id; INSERT into person_ex SELECT * from person where person.id = new.id; END IF; end;
削除トリガの作成:
create trigger person_trigger_delete AFTER DELETE on person for each row begin set @x = "trigger DELETE"; DELETE FROM person_ex where person_ex.id = old.id; end;
3.SQLコードのテスト:
INSERT INTO person (id,NAME,sex) VALUES (1,'chigo','M') ; INSERT INTO person (id,NAME,sex) VALUES (2,'Tony','F') ;
UPDATE person SET NAME = 'vara' , sex = 'F' WHERE id = 1 ; UPDATE person SET NAME = 'Tony' , sex = 'M' WHERE id = 1 ; UPDATE person SET NAME = 'vara' , sex = 'M' WHERE id = 1 ;
DELETE FROM person WHERE id = 1 ;