sybase触发器如下,现在要移植到oracle~~~CREATE TRIGGER tr_for_u_files ON V_Y_KHDA
FOR INSERT AS
declare getUserInfo cursor for
select HH from inserted
for read only
declare @user_no varchar(20)open getUserInfo
fetch getUserInfo into @user_no
while @@sqlstatus != 2
begin
if @@sqlstatus != 1
begin
DELETE FROM USER_FILES WHERE USER_NO = @user_no
if @@error != 0
begin
rollback
continue
end
INSERT INTO USER_FILES(USER_NO,USER_NAME,ADDRESS,VOLT_LEVEL_CODE,ALL_CAPACITY,CANCEL_DATE,HAVE_ADD_INFO)
SELECT HH,HM,YDDZ,convert(int,DYDJ),HTRL,XHRQ,"1" FROM inserted WHERE HH = @user_no
if @@error != 0
rollback tran
else
commit tran
end
fetch getUserInfo into @user_no
end
close getUserInfo
deallocate cursor getUserInfo我转成下面这样,但是有错误~~
CREATE OR REPLACE TRIGGER tr_for_u_files
AFTER INSERT
ON V_Y_KHDA
for each row
declare
user_no varchar2(20);
cursor getUserInfo is :new.HH;/*这个地方不知道怎么写,不知道这样写对不对*/
begin
open getUserInfo;
fetch getUserInfo into user_no;
while getUserInfo%found loop
begin
if getUserInfo%isopen then
begin
DELETE FROM USER_FILES WHERE USER_NO = user_no;
INSERT INTO USER_FILES(USER_NO,USER_NAME,ADDRESS,VOLT_LEVEL_CODE,ALL_CAPACITY,CANCEL_DATE,HAVE_ADD_INFO)
VALUES (:NEW.HH,:NEW.HM,:NEW.YDDZ,convert(int,:NEW.DYDJ),:NEW.HTRL,:NEW.XHRQ,'1');
commit;
exception when others then
rollback;
end;
end if;
fetch getUserInfo into user_no;
end;
end loop;
close getUserInfo;
end;
该怎么写啊~~高手帮忙~~谢谢各位!
FOR INSERT AS
declare getUserInfo cursor for
select HH from inserted
for read only
declare @user_no varchar(20)open getUserInfo
fetch getUserInfo into @user_no
while @@sqlstatus != 2
begin
if @@sqlstatus != 1
begin
DELETE FROM USER_FILES WHERE USER_NO = @user_no
if @@error != 0
begin
rollback
continue
end
INSERT INTO USER_FILES(USER_NO,USER_NAME,ADDRESS,VOLT_LEVEL_CODE,ALL_CAPACITY,CANCEL_DATE,HAVE_ADD_INFO)
SELECT HH,HM,YDDZ,convert(int,DYDJ),HTRL,XHRQ,"1" FROM inserted WHERE HH = @user_no
if @@error != 0
rollback tran
else
commit tran
end
fetch getUserInfo into @user_no
end
close getUserInfo
deallocate cursor getUserInfo我转成下面这样,但是有错误~~
CREATE OR REPLACE TRIGGER tr_for_u_files
AFTER INSERT
ON V_Y_KHDA
for each row
declare
user_no varchar2(20);
cursor getUserInfo is :new.HH;/*这个地方不知道怎么写,不知道这样写对不对*/
begin
open getUserInfo;
fetch getUserInfo into user_no;
while getUserInfo%found loop
begin
if getUserInfo%isopen then
begin
DELETE FROM USER_FILES WHERE USER_NO = user_no;
INSERT INTO USER_FILES(USER_NO,USER_NAME,ADDRESS,VOLT_LEVEL_CODE,ALL_CAPACITY,CANCEL_DATE,HAVE_ADD_INFO)
VALUES (:NEW.HH,:NEW.HM,:NEW.YDDZ,convert(int,:NEW.DYDJ),:NEW.HTRL,:NEW.XHRQ,'1');
commit;
exception when others then
rollback;
end;
end if;
fetch getUserInfo into user_no;
end;
end loop;
close getUserInfo;
end;
该怎么写啊~~高手帮忙~~谢谢各位!
解决方案 »
- Oracle语法问题
- oracle 在toad中怎么执行多条DDL语句
- Oracle查询一定时间段内每一段时间数据问题
- 请教一个PLSQL中,如果判断结果集是否为空的问题
- 还有一个Oracle触发器问题。。。
- ORACLE连接问题
- 请问为什么系统总说我的temp变量声明不对???
- 请问在RedHat上安装 Oracle9iDatabase时要设置那些内核参数?有没有相关文档给小弟一份!
- 请帮忙:关于日期的迷惑
- 8i 在 P4上如何装?在线等待,急,急,急,急,急,急,急,急,急,急!!!!
- 请问oracle的systimestamp和current_timestamp的问题
- oracle中如何执行存贮过程
--游标的声明(带参数)
CURSOR cur_nam(var_parmVARCHAR2) IS
select * from tab where col1 = var_parm;
begin
--打开游标
OPEN CPK_nam(:NEW.col);
--循环
FETCH CPK_namINTO LV_END_TIM;
---end;