errmsgdemo2.sql

来自「T-SQL示例大全」· SQL 代码 · 共 85 行

/* 文件名称: ErrMsgDemo2.sql */
USE 北风贸易
GO

if exists (select * from dbo.sysobjects where id = object_id(N'MyTable') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table MyTable
GO

CREATE TABLE MyTable
(
编号 char(5) NOT NULL PRIMARY KEY,
姓名 nvarchar(12) NOT NULL,
薪资 money NOT NULL,
部门 nvarchar(20) NOT NULL
)

GO

CREATE TRIGGER ActionTrigger
ON MyTable
AFTER INSERT,UPDATE
AS
-- 变量 @RecordTotal 用来记录新增或更新的个数
DECLARE @RecordTotal int,@NotQualifiedTotal int,@SucceedTotal int
-- @RecordTotal 记录新增或更新的个数
SET @RecordTotal = @@ROWCOUNT
-- @NotQualifiedTotal 记录不符合条件的个数
SELECT @NotQualifiedTotal = COUNT(*) FROM inserted WHERE 部门 = '资讯部' AND 薪资 < 30000
-- @SucceedTotal 记录符合条件的个数
SET @SucceedTotal = @RecordTotal - @NotQualifiedTotal
IF ( @NotQualifiedTotal > 0)
BEGIN
    IF @RecordTotal = 1
        BEGIN
        RAISERROR ('资讯部的薪资必须大于或等于 30000',16,1)
        ROLLBACK TRANSACTION
        END
    ELSE
        BEGIN
        DELETE MyTable FROM MyTable t,inserted i
             WHERE t.编号 = i.编号 AND
                           t.部门 = '资讯部' AND t.薪资 < 30000
        RAISERROR ('资讯部的薪资必须大于或等于 30000,不符者有 %d 人,其余 %d 个已顺利写入数据表',
                                16,1,@NotQualifiedTotal,@SucceedTotal)
        END
END

GO

/*--------------------------------开始新增数据记录--------------------------------*/
/*---------------------------请查看所返回的错误信息---------------------------*/
SET NOCOUNT ON
GO

INSERT MyTable VALUES ('E0001','章立民',50000,'资讯部')
INSERT MyTable VALUES ('E0002','王旭刚',40000,'财务部')
GO


BEGIN TRANSACTION
UPDATE MyTable SET 薪资 = 29000 WHERE 姓名 = '章立民'
COMMIT TRANSACTION
GO


DECLARE @TmpTable TABLE
(
编号 char(5) NOT NULL,
姓名 nvarchar(12) NOT NULL,
薪资 money NOT NULL,
部门 nvarchar(20) NOT NULL
)

INSERT @TmpTable VALUES ('E0003','陈小明',50000,'资讯部')
INSERT @TmpTable VALUES ('E0004','李大头',40000,'人事部')
INSERT @TmpTable VALUES ('E0005','王小华',10000,'资讯部')
INSERT @TmpTable VALUES ('E0006','陈小瑜',20000,'资讯部')
INSERT @TmpTable VALUES ('E0007','郑一正',40000,'资讯部')

BEGIN TRANSACTION
-- 一次新增多个
INSERT MyTable SELECT * FROM @TmpTable
COMMIT TRANSACTION
GO