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