触发器
1.如果用dbgrid形式编辑,用触发器限制比较好,系统能够提示用中文显示
CREATE TRIGGER [TRGJCDNAME] ON dbo.TABLEJCD
FOR INSERT, UPDATE
AS
DECLARE
@CODE VARCHAR(50),
@XS FLOAT,
@ROWCOUNT INT
--检查代码是否为空
//只支持1条操作,如果有多条操作则跳过
IF @@ROWCOUNT>1 RETURN
SELECT @CODE=LTRIM(ISNULL(CODE,'''')) FROM INSERTED
IF @CODE=''''
BEGIN
ROLLBACK TRAN
RAISERROR(''代码不允许为空或空格!'',16,1)
END
--检查代码是否有重复的
SELECT @ROWCOUNT=COUNT(*) FROM TABLEJCD,INSERTED
WHERE UPPER(TABLEJCD.CODE)=UPPER(INSERTED.CODE) AND (INSERTED.SYSTEMID=TABLEJCD.SYSTEMID)
--如果有重复
IF @ROWCOUNT>1
BEGIN
ROLLBACK TRAN
RAISERROR(''代码重复,请修改!'',16,1)
END
--不允许为0或负数
SELECT @XS=ISNULL(XS,0) FROM INSERTED
IF @XS<=0
BEGIN
ROLLBACK TRAN
RAISERROR(''不允许为0或负数!'',16,1)
END
2.当操作多条记录时(及联删除或更新)
create trigger del_id on tablename for delete
as
delete from tablename where id in(select id from deleted)
3.递规触发器
create tablebudget
(
dept_name varchar(30) not null,
part_name varchar(30) null,
budget_amt money not null)
在上面的表,记录之间是关联,比如部门a,隶属于部门b,顶级部门的父部门为空,我们想到,子部门的预算更新时,其对应的父部门的预算,也要更新
create trigger update_budget
on budget for update as
if (@@rowcount>1)
begin
print ''only on row can be update at a time''
rollback tran
return
end
if (select parent_name from inserted ) is null return
update
set budget_amt=budget_amt+(select budget_amt from inserted)-
(select budget_amt from deleted)
where dept_name=(select parent_name from inserted)
更新时,其对应的父部门
用游标控制还不会,希望大家跟帖。还有其他的好的帖子。