触发器学习《一》 简单触发器创建

--1、创建表
create table userinfo(
id number(10,0) primary key,
       name varchar2(40)
);

--2、创建序列
create sequence userinfo_seq start with 1 maxvalue 9999999999 increment by 1;

--3、创建触发器
create or replace
trigger userinfo_trigger
before insert on userinfo
for each row
begin
select userinfo_seq.nextval into :new.id from dual;
end ;

--4、插入数据
insert into userinfo(name) values('user1');
insert into userinfo(name) values('user2');

--5、查看运行效果

select * from userinfo;

结合需求示例：

/**

需求：

动态取值，并同步另一张表中

*/

create or replace trigger user_trigger_pro_test
after insert or update or delete on test_user_gj --注意after使用
for each row
declare
     getname varchar(20);  
    getstr varchar(20);         
begin
  if inserting then
      select name  into getname from test_user_center where id= :new.id; --注意赋值
      insert into test_user_pf(user_id,user_name)
                values(:new.id, getname);
  end if;
end;

测试 

insert into TEST_USER_center(id,name) values('a','aaa') -- 测试的时候，注先录入这句话，否则会提示找不到数据的现象

insert into test_user_gj(id,name)values('a','aaagj4');


select * from Test_User_Gj;
select * from test_user_pf;
select * from TEST_USER_center;


delete from Test_User_Gj;
delete from test_user_pf;
delete from TEST_USER_center
drop trigger user_trigger_pro_test;

/**

表创建

**/

-- Create table
create table TEST_USER_GJ
(
  id   NVARCHAR2(10),
  name NVARCHAR2(10)
)
tablespace PLANTFORM_DATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    next 1
    minextents 1
    maxextents unlimited

  );

-- Create table
create table TEST_USER_PF
(
  user_id   NVARCHAR2(10),
  user_name NVARCHAR2(10)
)
tablespace PLANTFORM_DATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    next 1
    minextents 1
    maxextents unlimited
  );

-- Create table
create table TEST_USER_CENTER
(
  id   NVARCHAR2(10),
  name NVARCHAR2(10)
)
tablespace PLANTFORM_DATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 16
    next 8
    minextents 1
    maxextents unlimited
  );

示例：

示例一

create or replace trigger test_user_trigger

before insert on test_user
for each row
begin
  insert into userinfo select * from test_user
end ;

示例二
create or replace trigger test_user_trigger
    before insert on test_user
    for each row
begin
  if inserting then
      insert into userinfo(id,name) 
      values (5,'test');
  end if;

end;

示例三
create or replace trigger test_user_trigger
    after insert or update or delete on test_user
    for each row
begin
   if deleting then
      delete from userinfo where id=:old.user_id;
  end if;
  if inserting then
      insert into userinfo(id,name)
      values(:new.user_id,:new.user_name);
  end if;
  if updating then
     update userinfo set name=:new.user_name where id=:old.user_id;
  end if;
end;

示例四

create or replace trigger a_b_test
after insert or update or delete 
on a_test
for each row
begin
if deleting then
delete from b_test where id=:old.id;
end if;
if inserting then
insert into b_test(id,username,password) //b_test表的字段
values(:new.id,:new.name,:new.pass); //a_test表的字段
end if;
if updating then
update b_test set username=:new.name,password=:new.pass where id=:old.id;
end if;
end a_b_test;