中国石油大学(华东)数据库原理实验答案(sql语句) 下载本文

UPDATE COST

SET 结算金额=@JS_MONEY

WHERE 单据号=(select 单据号 from inserted); GO

INSERT

INTO COST(单据号,预算单位,井号,预算金额,预算人,预算日期,开工日期,完工日期,施工单位,施工内容,人工费,设备费, 材料费,其他费用,结算人,结算日期,入账金额,入账人,入账日期)

VALUES('zy2016007','112201001','y002','10000.00','张三

','2016-5-1','2016-5-4','2016-5-25','作业公司作业一队','堵漏

','250','100','140','700','李四','2016-5-26','11900','王五','2016-5-28');

ifOBJECT_ID('TRR2','TR')isnotnull droptrigger TRR2 GO

createtrigger TRR2 on COST AFTERUPDATE AS

DECLARE

@JS_MONEY MONEY; DECLARE

@MA_MONEY MONEY, @PE_MONEY MONEY, @EQ_MONEY MONEY, @EL_MONEY MONEY;

SELECT @MA_MONEY=材料费,@PE_MONEY=人工费,@EQ_MONEY=设备费,@EL_MONEY=其他费用 FROM COST

SET @JS_MONEY=@MA_MONEY+@PE_MONEY+@EQ_MONEY+@EL_MONEY;

UPDATE COST

SET 结算金额=@JS_MONEY

WHERE 单据号=(select 单据号 from inserted); GO

UPDATE COST SET 入账金额=1

WHERE 单据号='zy2016005';

ifOBJECT_ID('TRR3','TR')isnotnull droptrigger TRR3 GO

createtrigger TRR3 on COST AFTERDELETE AS

DELETEFROM MATERIALPRICE

WHERE 单据号=(select 单据号 from deleted); GO

DELETEFROM COST

WHERE 单据号='zy2016007'; GO

CREATEVIEW CY1 AS

SELECT 单据号,预算单位,COST.井号,预算金额,预算人,预算日期,开工日期,完工日期, 施工单位,施工内容,人工费,设备费, 材料费,其他费用,结算金额,结算人,结算日期, 入账金额,入账人,入账日期,井别,单位名称 FROM COST,OILWELL,UNITNO

WHERE 预算单位=OILWELL.单位代码 AND 预算单位=UNITNO.单位代码; GO

CREATELOGIN USER01 WITHPASSWORD='1234'; CREATEUSER USER11 FORLOGIN USER01;

GRANTSELECT ON CY1 TO USER11;

CREATELOGIN USER02 WITHPASSWORD='6789'; CREATEUSER USER12 FORLOGIN USER02; GO

SETUSER'USER12' GO

EXECUTE

YUNXING_SITU@DANWEIDAIMA='1122',@STARTTIME='2016-5-1',@ENDTIME='2016-5-29'; EXECUTE

YUNXING_SITU@DANWEIDAIMA='112201',@STARTTIME='2016-5-1',@ENDTIME='2016-5-29';

IFOBJECT_ID('TRR2','TR')ISNOTNULL DROPTRIGGER TRR2

GO

CREATETRIGGER TRR2 ON COST AFTERUPDATE AS

DECLARE @NOWTIME DATETIME SET @NOWTIME GETDATE();

DECLARE @NOWTIME VARCHAR(10)

SET @NOWTIME=CONVERT(VARCHAR(10),UPDATE(DW,@NOWTIME)-1);

IF @NOWTIME NOTBETWEEN 1 AND 5 BEGIN

IF@@TRANCOUNT>0 BEGIN ROLLBACK

PRINT'非工作日不能更新数据' END END GO

BEGINTRANSACTION UPDATE COST

SET 预算人='赵铁柱'

WHERE 单据号='zy2011001'

IF@@TRANCOUNT>0 BEGIN COMMIT END GO