sybase触发器转成oracle~~大家帮忙~
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;
该怎么写啊~~高手帮忙~~谢谢各位!
[解决办法]
oracle中无需定义游标了。
[解决办法]
CREATE OR REPLACE TRIGGER tr_for_u_files
AFTER INSERT
ON V_Y_KHDA
for each row
begin
DELETE FROM USER_FILES WHERE USER_NO = :NEW.HH;
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,to_number(:NEW.DYDJ),:NEW.HTRL,:NEW.XHRQ, '1 ');
commit;
exception when others then
rollback;
end;
