-- Create table
alter table T_SYSUSER
(
id NUMBER not null,
username VARCHAR2(20),
password VARCHAR2(20),
isactive NUMBER
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table T_SYSUSER
add constraint ID primary key (ID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);-- Create sequence
create sequence S_T_SYSUSER
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20
order;-- Create table
alter table T_EMPLOYEE
(
empid NUMBER not null,
loginname VARCHAR2(50),
name VARCHAR2(50),
sex VARCHAR2(10),
age NUMBER,
roleid NUMBER,
teamid NUMBER
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table T_EMPLOYEE
add constraint EMPID primary key (EMPID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create sequence
create sequence S_T_EMPLOYEE
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20
order;现然要同步更新二张表的数据触发器如下:create or replace trigger trg_LoginUser
before INSERT or UPDATE or DELETE
ON t_Employee
FOR Each Row
declare
LoginName varchar2(50);
Pwd varchar2(50);
UserID Number;
begin
Integritypackage.NextNestLevel;
--- insert
IF inserting
begin select UserID=:New.EmpID,LoginName=:New.LoginName
insert into T_sysuser(id,UserName,Password,IsActive) values(UserID,LoginName,'CF79AE6ADDBA60AD018347359BD144D2','1')
end
--- update
IF updating
begin
select UserID =:Old.EmpID
select LoginName =:New.LoginName update T_sysuser set UserName=LoginName where id=UserID
end --- delete
IF deleting
begin
select UserID = :Old.EmpID
delete from T_sysuser where id = UserID
end
end报错:TRIGGER GUOZG.TRG_LOGINUSER 编译错误错误:PLS-00103: 出现符号 "BEGIN"在需要下列之一时:
. ( * @ % & = - + < / >
at in is mod remainder not rem then <an exponent (**)>
<> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_
between || multiset member SUBMULTISET_
行:14
文本:select UserID=:New.EmpID,LoginName=:New.LoginName请大家帮忙看下!
alter table T_SYSUSER
(
id NUMBER not null,
username VARCHAR2(20),
password VARCHAR2(20),
isactive NUMBER
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table T_SYSUSER
add constraint ID primary key (ID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);-- Create sequence
create sequence S_T_SYSUSER
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20
order;-- Create table
alter table T_EMPLOYEE
(
empid NUMBER not null,
loginname VARCHAR2(50),
name VARCHAR2(50),
sex VARCHAR2(10),
age NUMBER,
roleid NUMBER,
teamid NUMBER
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table T_EMPLOYEE
add constraint EMPID primary key (EMPID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create sequence
create sequence S_T_EMPLOYEE
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20
order;现然要同步更新二张表的数据触发器如下:create or replace trigger trg_LoginUser
before INSERT or UPDATE or DELETE
ON t_Employee
FOR Each Row
declare
LoginName varchar2(50);
Pwd varchar2(50);
UserID Number;
begin
Integritypackage.NextNestLevel;
--- insert
IF inserting
begin select UserID=:New.EmpID,LoginName=:New.LoginName
insert into T_sysuser(id,UserName,Password,IsActive) values(UserID,LoginName,'CF79AE6ADDBA60AD018347359BD144D2','1')
end
--- update
IF updating
begin
select UserID =:Old.EmpID
select LoginName =:New.LoginName update T_sysuser set UserName=LoginName where id=UserID
end --- delete
IF deleting
begin
select UserID = :Old.EmpID
delete from T_sysuser where id = UserID
end
end报错:TRIGGER GUOZG.TRG_LOGINUSER 编译错误错误:PLS-00103: 出现符号 "BEGIN"在需要下列之一时:
. ( * @ % & = - + < / >
at in is mod remainder not rem then <an exponent (**)>
<> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_
between || multiset member SUBMULTISET_
行:14
文本:select UserID=:New.EmpID,LoginName=:New.LoginName请大家帮忙看下!
--你怎么把mssql和oracle給混了,暈
...
--- insert
IF inserting then
UserID :=:New.EmpID;
LoginName:=:New.LoginName;
insert into T_sysuser(id,UserName,Password,IsActive) values(UserID,LoginName,'CF79AE6ADDBA60AD018347359BD144D2','1')
end if;
--- update
IF updating then
UserID :=:Old.EmpID;
LoginName :=:New.LoginName;
update T_sysuser set UserName=LoginName where id=UserID;
end if; --- delete
IF deleting then
UserID := :Old.EmpID;
delete from T_sysuser where id = UserID;
end if;
end;