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_testafter 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') -- 测试的时候,注先录入这句话,否则会提示找不到数据的现象
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 tablecreate 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_userfor 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_testafter 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;