34,590
社区成员
发帖
与我相关
我的任务
分享
CREATE TRIGGER purtb_a ON PURTB
AFTER INSERT ,UPDATE
AS
BEGIN
DECLARE @TB009 ,
@UDF51 ----自己补充数据类型
IF EXISTS(SELECT 1 FROM INSERTED) AND NOT EXISTS(SELECT 1 FROM DELETED) --INSERT
BEGIN
UPDATE INSERTED SET UDF51=TB009
END
IF EXISTS(SELECT 1 FROM INSERTED) AND EXISTS(SELECT 1 FROM DELETED) --UPDATE
BEGIN
IF UPDATE(TB009)
BEGIN
SELECT @UDF51=UDF51 FROM DELETED
SELECT @TB009=TB009 FROM INSERTED
IF TB009>(@UDF01*0.1)
ROLLBACK TRAN;
END
END
END
CREATE TRIGGER PURTBUpdate ON PURTB
INSTEAD OF UPDATE
as
BEGIN
DECLARE @UDF51 INT
DECLARE @TB009 INT
SET @UDF51 = (SELECT TOP 1 UDF51 FROM PURTB WHERE id =( SELECT TOP 1 id FROM Inserted))
SET @TB009 = (SELECT TOP 1 TB009 FROM Inserted)
IF @TB009>@UDF51*0.1
BEGIN
raiserror('错误',16,8)
rollback tran
END
ELSE
UPDATE PURTB SET TB009=@TB009 WHERE id=( SELECT TOP 1 id FROM Inserted)
END
UPDATE dbo.PURTB SET TB009=100 WHERE id=2
SELECT * FROM dbo.PURTB
CREATE TRIGGER PURTBInsert ON PURTB
INSTEAD OF INSERT
as
BEGIN
INSERT INTO PURTB
( TB009, UDF51 )
VALUES ( (SELECT TOP 1 TB009 FROM Inserted), -- TB009 - int
(SELECT TOP 1 TB009 FROM Inserted) -- UDF51 - int
)
END
--测试数据
if not object_id(N'Tempdb..PURTB') is null
drop table PURTB
Go
Create table PURTB(id INT IDENTITY,[TB009] int,[UDF51] int)
Go
--测试数据结束
INSERT INTO dbo.PURTB
( TB009 )
VALUES ( 1
)
SELECT * FROM dbo.PURTB