下面是我在sql中写好的触发器,因为没写过oracle的,大家帮忙看下,在oracle里这个该怎么写
alter trigger TongJi_Report
on study
for update
as
declare
@str_StudyInstanceUID varchar(120),
@str_ReportStatus varchar(10),
@str_AccessionNumber varchar(120),
@str_PatientNameC varchar(10),
@str_PatientID varchar(64),
@str_PatientSex varchar(10),
@str_PatientBirthday datetime,
@str_StudyClass2 varchar(50),
@str_SeriesDate datetime,
@str_laiyuan varchar(10),
@str_RECEIVED_CODE varchar(50),
@str_shenqingYiShi varchar(50),
@str_ReportingPhysician varchar(50),
@str_FactPrice varchar(50),
@str_InsuranceType varchar(50),
@str_jianchasuojian varchar(4000),
@str_jianchajielun varchar(4000),
@str_ReportFileLocate varchar(120),
@str_ReportFileName varchar(120)
if update(ReportStatus) select @str_StudyInstanceUID=StudyInstanceUID,@str_ReportStatus=ReportStatus from insertedselect @str_PatientID=PatientID,@str_PatientNameC=PatientNameC,@str_PatientSex=PatientSex,@str_PatientBirthday=PatientBirthday,@str_AccessionNumber=AccessionNumber,
@str_StudyClass2=StudyClass2,@str_SeriesDate=SeriesDate,@str_laiyuan=laiyuan,@str_RECEIVED_CODE=RECEIVED_CODE,
@str_shenqingYiShi=shenqingYiShi,@str_ReportingPhysician=ReportingPhysician,@str_FactPrice=FactPrice,
@str_InsuranceType=InsuranceType,@str_jianchasuojian=study.jianchasuojian,@str_jianchajielun=study.jianchajielun,
@str_ReportFileLocate=StudyComponent.ReportFileLocate,@str_ReportFileName=StudyComponent.ReportFileName
from Patient,Study,Series,StudyComponent where Patient.PatientUID=study.PatientUID and
Study.StudyInstanceUID=Series.StudyInstanceUID and Series.StudyInstanceUID=StudyComponent.StudyInstanceUID and
Study.StudyInstanceUID =@str_StudyInstanceUID and studycomponent.reportfiletype='jpg'if(@str_ReportStatus=8 and @str_laiyuan='门诊' and @str_AccessionNumber is not null and @str_AccessionNumber<>'')
begin
if @str_StudyInstanceUID is null
begin
select @str_StudyInstanceUID=''
end
if @str_ReportStatus is null
begin
select @str_ReportStatus=''
end
if @str_AccessionNumber is null
begin
select @str_AccessionNumber=''
end
if @str_PatientNameC is null
begin
select @str_PatientNameC=''
end
if @str_PatientSex is null
begin
select @str_PatientSex=''
end
if @str_PatientBirthday is null
begin
select @str_PatientBirthday=getdate()
end
if @str_StudyClass2 is null
begin
select @str_StudyClass2=''
end
if @str_SeriesDate is null
begin
select @str_SeriesDate=getdate()
end
if @str_laiyuan is null
begin
select @str_laiyuan=''
end
if @str_RECEIVED_CODE is null
begin
select @str_RECEIVED_CODE=''
end
if @str_shenqingYiShi is null
begin
select @str_shenqingYiShi=''
end
if @str_ReportingPhysician is null
begin
select @str_ReportingPhysician=''
end
if @str_FactPrice is null
begin
select @str_FactPrice=''
end
if @str_InsuranceType is null
begin
select @str_InsuranceType=''
end
if @str_jianchasuojian is null
begin
select @str_jianchasuojian=''
end
if @str_jianchajielun is null
begin
select @str_jianchajielun=''
end
if @str_ReportFileLocate is null
begin
select @str_ReportFileLocate=''
end
if @str_ReportFileName is null
begin
select @str_ReportFileName=''
end
if @str_PatientID is null
begin
select @str_PatientID=''
end
INSERT INTO pacstohis.dbo.PACSTOHIS(CreateDateTime,ExecuteStatus,InfoType,ExecuteInfo)
VALUES (getdate(),'0', '4','study~AccessionNumber~ST~=~'+ @str_AccessionNumber +
'~|study~PatientID~ST~=~'+ @str_PatientID +
'~|study~PatientNameC~ST~=~'+ @str_PatientNameC +
'~|study~PatientSex~ST~=~'+ @str_PatientSex +
'~|study~PatientBith~DA~=~'+ convert(nvarchar(120),@str_PatientBirthday,120) +
'~|study~StudyClass2~ST~=~'+ @str_StudyClass2 +
'~|study~ECG~ST~=~ECG'+
'~|study~SeriesDate~DA~=~'+ convert(nvarchar(120),@str_SeriesDate,120) +
'~|study~laiyuan~ST~=~'+@str_laiyuan +
'~|study~Received_code~ST~=~'+@str_RECEIVED_CODE +
'~|study~shenqingyishi~ST~=~'+ @str_shenqingYiShi +
'~|study~BGDate~ST~=~'+convert(nvarchar(120),getdate(),120)+
'~|study~ReportPhysiscian~ST~=~'+@str_ReportingPhysician +
'~|study~Status~ST~=~4'+
'~|study~FactPrice~ST~=~'+@str_FactPrice +
'~|study~InsertType~ST~=~'+@str_InsuranceType +
'~|study~DM~ST~=~310701001B'+
'~|study~jianchasuojian~ST~=~'+@str_jianchasuojian +
'~|study~jianchajielun~ST~=~'+@str_jianchajielun +
'~|study~JPG~ST~=~jpg'+
'~|study~ReportFileName~ST~=~'+@str_ReportFileName +
'~|study~Path~ST~=~\\128.0.150.140\Images\'+ REPLACE(RIGHT(@str_ReportFileLocate, LEN(@str_ReportFileLocate) - 3)+@str_ReportFileName, '\', '\') +'~')
end
alter trigger TongJi_Report
on study
for update
as
declare
@str_StudyInstanceUID varchar(120),
@str_ReportStatus varchar(10),
@str_AccessionNumber varchar(120),
@str_PatientNameC varchar(10),
@str_PatientID varchar(64),
@str_PatientSex varchar(10),
@str_PatientBirthday datetime,
@str_StudyClass2 varchar(50),
@str_SeriesDate datetime,
@str_laiyuan varchar(10),
@str_RECEIVED_CODE varchar(50),
@str_shenqingYiShi varchar(50),
@str_ReportingPhysician varchar(50),
@str_FactPrice varchar(50),
@str_InsuranceType varchar(50),
@str_jianchasuojian varchar(4000),
@str_jianchajielun varchar(4000),
@str_ReportFileLocate varchar(120),
@str_ReportFileName varchar(120)
if update(ReportStatus) select @str_StudyInstanceUID=StudyInstanceUID,@str_ReportStatus=ReportStatus from insertedselect @str_PatientID=PatientID,@str_PatientNameC=PatientNameC,@str_PatientSex=PatientSex,@str_PatientBirthday=PatientBirthday,@str_AccessionNumber=AccessionNumber,
@str_StudyClass2=StudyClass2,@str_SeriesDate=SeriesDate,@str_laiyuan=laiyuan,@str_RECEIVED_CODE=RECEIVED_CODE,
@str_shenqingYiShi=shenqingYiShi,@str_ReportingPhysician=ReportingPhysician,@str_FactPrice=FactPrice,
@str_InsuranceType=InsuranceType,@str_jianchasuojian=study.jianchasuojian,@str_jianchajielun=study.jianchajielun,
@str_ReportFileLocate=StudyComponent.ReportFileLocate,@str_ReportFileName=StudyComponent.ReportFileName
from Patient,Study,Series,StudyComponent where Patient.PatientUID=study.PatientUID and
Study.StudyInstanceUID=Series.StudyInstanceUID and Series.StudyInstanceUID=StudyComponent.StudyInstanceUID and
Study.StudyInstanceUID =@str_StudyInstanceUID and studycomponent.reportfiletype='jpg'if(@str_ReportStatus=8 and @str_laiyuan='门诊' and @str_AccessionNumber is not null and @str_AccessionNumber<>'')
begin
if @str_StudyInstanceUID is null
begin
select @str_StudyInstanceUID=''
end
if @str_ReportStatus is null
begin
select @str_ReportStatus=''
end
if @str_AccessionNumber is null
begin
select @str_AccessionNumber=''
end
if @str_PatientNameC is null
begin
select @str_PatientNameC=''
end
if @str_PatientSex is null
begin
select @str_PatientSex=''
end
if @str_PatientBirthday is null
begin
select @str_PatientBirthday=getdate()
end
if @str_StudyClass2 is null
begin
select @str_StudyClass2=''
end
if @str_SeriesDate is null
begin
select @str_SeriesDate=getdate()
end
if @str_laiyuan is null
begin
select @str_laiyuan=''
end
if @str_RECEIVED_CODE is null
begin
select @str_RECEIVED_CODE=''
end
if @str_shenqingYiShi is null
begin
select @str_shenqingYiShi=''
end
if @str_ReportingPhysician is null
begin
select @str_ReportingPhysician=''
end
if @str_FactPrice is null
begin
select @str_FactPrice=''
end
if @str_InsuranceType is null
begin
select @str_InsuranceType=''
end
if @str_jianchasuojian is null
begin
select @str_jianchasuojian=''
end
if @str_jianchajielun is null
begin
select @str_jianchajielun=''
end
if @str_ReportFileLocate is null
begin
select @str_ReportFileLocate=''
end
if @str_ReportFileName is null
begin
select @str_ReportFileName=''
end
if @str_PatientID is null
begin
select @str_PatientID=''
end
INSERT INTO pacstohis.dbo.PACSTOHIS(CreateDateTime,ExecuteStatus,InfoType,ExecuteInfo)
VALUES (getdate(),'0', '4','study~AccessionNumber~ST~=~'+ @str_AccessionNumber +
'~|study~PatientID~ST~=~'+ @str_PatientID +
'~|study~PatientNameC~ST~=~'+ @str_PatientNameC +
'~|study~PatientSex~ST~=~'+ @str_PatientSex +
'~|study~PatientBith~DA~=~'+ convert(nvarchar(120),@str_PatientBirthday,120) +
'~|study~StudyClass2~ST~=~'+ @str_StudyClass2 +
'~|study~ECG~ST~=~ECG'+
'~|study~SeriesDate~DA~=~'+ convert(nvarchar(120),@str_SeriesDate,120) +
'~|study~laiyuan~ST~=~'+@str_laiyuan +
'~|study~Received_code~ST~=~'+@str_RECEIVED_CODE +
'~|study~shenqingyishi~ST~=~'+ @str_shenqingYiShi +
'~|study~BGDate~ST~=~'+convert(nvarchar(120),getdate(),120)+
'~|study~ReportPhysiscian~ST~=~'+@str_ReportingPhysician +
'~|study~Status~ST~=~4'+
'~|study~FactPrice~ST~=~'+@str_FactPrice +
'~|study~InsertType~ST~=~'+@str_InsuranceType +
'~|study~DM~ST~=~310701001B'+
'~|study~jianchasuojian~ST~=~'+@str_jianchasuojian +
'~|study~jianchajielun~ST~=~'+@str_jianchajielun +
'~|study~JPG~ST~=~jpg'+
'~|study~ReportFileName~ST~=~'+@str_ReportFileName +
'~|study~Path~ST~=~\\128.0.150.140\Images\'+ REPLACE(RIGHT(@str_ReportFileLocate, LEN(@str_ReportFileLocate) - 3)+@str_ReportFileName, '\', '\') +'~')
end
解决方案 »
- C#回调函数的问题
- winform里使用用户自定义控件,手动设置属性值无法获取
- 最近想做一个Excel报表,各位大侠都用什么做啊?
- 用vs2005开发存储过程遇到的数据库连接失败的问题
- 急:winform下datagrid的CurrentCellChanged事件,不可理解的怪问题
- 急急急!!如何将excel嵌入到C# ASP.NET 窗体里?
- Winform操作Word遇到80080005 服务器运行失败 (异常来自 HRESULT:0x80080005 (CO_E_SERVER_EXEC_FA
- 这个网站 精确的北京时间 是如何获取的
- 一个查询求和语句的问题
- 急!!! C# 中存储过程返回值
- 怎么实现“选择文件打开方式”的
- winform正则获取link标签中的href属性
a表字段【ID,字段1,字段2】
b表字段【ID,SD,字段1,字段2,status】
c表字段【SD,字段1,字段2】
d表字段【SD,字段1,字段2】
触发条件 当b表中status字段被更改,就把四个表里的字段 触发到另外一张表里字段关联 a.ID=b.ID and b.SD=c.SD and C.SD=d.SD
after update on study
for each row
declare
-- 这里声明变量,不要@前缀
begin
--这里定义内容,访问更新前的内容使用:old.列名,访问更新后的内容使用:new.列名
end test;
after update on study
for each row
declare
-- 这里声明变量,不要@前缀
begin
--这里定义内容,访问更新前的内容使用:old.列名,访问更新后的内容使用:new.列名
end TongJi_Report;
select StudyInstanceUID=:old.StudyInstanceUID,ReportStatus=:old.ReportStatus,AccessionNumber=:old.AccessionNumber from inserted;select PatientID=:old.PatientID,PatientNameC=:old.PatientNameC,PatientSex=:old.PatientSex,PatientBirthday=:old.PatientBirthday,
StudyClass2=:old.StudyClass2,SeriesDate=:old.SeriesDate,laiyuan=:old.laiyuan,RECEIVED_CODE=:old.RECEIVED_CODE,
shenqingYiShi=:old.shenqingYiShi,ReportingPhysician=:old.ReportingPhysician,FactPrice=:old.FactPrice,
InsuranceType=:old.InsuranceType,jianchasuojian=:new.jianchasuojian,jianchajielun=:new.jianchajielun,
ReportFileLocate=:old.StudyComponent.ReportFileLocate,ReportFileName=:old.StudyComponent.ReportFileName
from patient,study,series,studycomponent where patient.patientuid=:old.study.patientuid and
study.StudyInstanceUID=:old.series.StudyInstanceUID and series.StudyInstanceUID=:old.studycomponent.StudyInstanceUID and
study.StudyInstanceUID=:old.StudyInstanceUID and studycomponent.reportfiletype='jpg'
SELECT :old.StudyInstanceUID INTO StudyInstanceUID FROM DUAL;
或者
StudyInstanceUID := :old.StudyInstanceUID;
我这里假设存在一个列叫做StudyInstanceUID
记住,Oracle里面没有inserted临时表。