不用触发器。 update 表格的时候,直接加上限制条件,比如: where 列 is not null
大家好:因为我用的是Navicat; 这个可以直接修改数据库里的内容的;所以好像用不了where 列 is not null 这样的限制条件;所以想写一个触发器来实现。求教大家了,麻烦了!
USE tempdb GO if OBJECT_ID ('Test') is not null drop table dbo.Test_fun; CREATE TABLE Test(id INT IDENTITY(1,1), NAME NVARCHAR(10),sex NVARCHAR(2)) GOCreate TRIGGER [dbo].[Trg_Test_Update] ON [dbo].[Test] AFTER UPDATE AS BEGIN DECLARE @id INT, @newNAME VARCHAR(10), @oldNAME VARCHAR(10), @newsex VARCHAR(2), @oldsex NVARCHAR(2), @errMsg NVARCHAR(200) SET @id = 0 SELECT TOP 1 @id = [id],@oldNAME = isnull(NAME,''),@oldsex=ISNULL(sex,'') FROM deleted WHERE [id] > @id ORDER BY [id] ASC WHILE @@ROWCOUNT = 1 BEGIN SELECT TOP 1 @id = [id],@newNAME = isnull(NAME,''),@newsex=ISNULL(sex,'') FROM inserted WHERE [id] = @id
IF(@oldNAME<>'' AND @newNAME<>@oldNAME) BEGIN SET @errMsg='有值,不允许更新!' GOTO Error_Handle END IF(@oldsex<>'' AND @newsex<>@oldsex) BEGIN SET @errMsg='有值,不允许更新!' GOTO Error_Handle END SELECT TOP 1 @id = [id],@oldNAME = isnull(NAME,''),@oldsex=ISNULL(sex,'') FROM deleted WHERE [id] > @id ORDER BY [id] ASC END RETURN Error_Handle: RAISERROR (@errMsg,16,1) WITH NOWAIT ROLLBACK TRANSACTION RETURN END GO
USE tempdb GO if OBJECT_ID ('Test') is not null drop table dbo.Test; CREATE TABLE Test(id INT IDENTITY(1,1), NAME NVARCHAR(10),sex NVARCHAR(2)) GOINSERT INTO test(NAME,sex)VALUES(NULL,NULL) INSERT INTO test(NAME,sex)VALUES('11','') INSERT INTO test(NAME,sex)VALUES(NULL,'2')Create TRIGGER [dbo].[Trg_Test_Update] ON [dbo].[Test] AFTER UPDATE AS BEGIN DECLARE @id INT, @newNAME VARCHAR(10), @oldNAME VARCHAR(10), @newsex VARCHAR(2), @oldsex NVARCHAR(2), @errMsg NVARCHAR(200) SET @id = 0 SELECT TOP 1 @id = [id],@oldNAME = isnull(NAME,''),@oldsex=ISNULL(sex,'') FROM deleted WHERE [id] > @id ORDER BY [id] ASC WHILE @@ROWCOUNT = 1 BEGIN SELECT TOP 1 @id = [id],@newNAME = isnull(NAME,''),@newsex=ISNULL(sex,'') FROM inserted WHERE [id] = @id
IF(@oldNAME<>'' AND @newNAME<>@oldNAME) BEGIN SET @errMsg='有值,不允许更新!' GOTO Error_Handle END IF(@oldsex<>'' AND @newsex<>@oldsex) BEGIN SET @errMsg='有值,不允许更新!' GOTO Error_Handle END SELECT TOP 1 @id = [id],@oldNAME = isnull(NAME,''),@oldsex=ISNULL(sex,'') FROM deleted WHERE [id] > @id ORDER BY [id] ASC END RETURN Error_Handle: RAISERROR (@errMsg,16,1) WITH NOWAIT ROLLBACK TRANSACTION RETURN END GO SELECT * FROM testUPDATE test SET NAME ='22' WHERE id=2 消息 50000,级别 16,状态 1,过程 Trg_Test_Update,第 33 行 有值,不允许更新! 消息 3609,级别 16,状态 1,第 1 行 事务在触发器中结束。批处理已中止。UPDATE test SET sex='3' WHERE id=1 (1 行受影响)
--为空或者为null值才执行. 有值则不执行 update tb set col='XX' where col is null or Rtrim(col)=''
update 表格的时候,直接加上限制条件,比如:
where 列 is not null
USE tempdb
GO
if OBJECT_ID ('Test') is not null drop table dbo.Test_fun;
CREATE TABLE Test(id INT IDENTITY(1,1), NAME NVARCHAR(10),sex NVARCHAR(2))
GOCreate TRIGGER [dbo].[Trg_Test_Update]
ON [dbo].[Test]
AFTER UPDATE
AS
BEGIN
DECLARE @id INT,
@newNAME VARCHAR(10),
@oldNAME VARCHAR(10),
@newsex VARCHAR(2),
@oldsex NVARCHAR(2),
@errMsg NVARCHAR(200)
SET @id = 0
SELECT TOP 1 @id = [id],@oldNAME = isnull(NAME,''),@oldsex=ISNULL(sex,'') FROM deleted WHERE [id] > @id ORDER BY [id] ASC
WHILE @@ROWCOUNT = 1
BEGIN
SELECT TOP 1 @id = [id],@newNAME = isnull(NAME,''),@newsex=ISNULL(sex,'') FROM inserted WHERE [id] = @id
IF(@oldNAME<>'' AND @newNAME<>@oldNAME)
BEGIN
SET @errMsg='有值,不允许更新!'
GOTO Error_Handle
END
IF(@oldsex<>'' AND @newsex<>@oldsex)
BEGIN
SET @errMsg='有值,不允许更新!'
GOTO Error_Handle
END
SELECT TOP 1 @id = [id],@oldNAME = isnull(NAME,''),@oldsex=ISNULL(sex,'') FROM deleted WHERE [id] > @id ORDER BY [id] ASC
END
RETURN
Error_Handle:
RAISERROR (@errMsg,16,1) WITH NOWAIT
ROLLBACK TRANSACTION
RETURN
END
GO
GO
if OBJECT_ID ('Test') is not null drop table dbo.Test;
CREATE TABLE Test(id INT IDENTITY(1,1), NAME NVARCHAR(10),sex NVARCHAR(2))
GOINSERT INTO test(NAME,sex)VALUES(NULL,NULL)
INSERT INTO test(NAME,sex)VALUES('11','')
INSERT INTO test(NAME,sex)VALUES(NULL,'2')Create TRIGGER [dbo].[Trg_Test_Update]
ON [dbo].[Test]
AFTER UPDATE
AS
BEGIN
DECLARE @id INT,
@newNAME VARCHAR(10),
@oldNAME VARCHAR(10),
@newsex VARCHAR(2),
@oldsex NVARCHAR(2),
@errMsg NVARCHAR(200)
SET @id = 0
SELECT TOP 1 @id = [id],@oldNAME = isnull(NAME,''),@oldsex=ISNULL(sex,'') FROM deleted WHERE [id] > @id ORDER BY [id] ASC
WHILE @@ROWCOUNT = 1
BEGIN
SELECT TOP 1 @id = [id],@newNAME = isnull(NAME,''),@newsex=ISNULL(sex,'') FROM inserted WHERE [id] = @id
IF(@oldNAME<>'' AND @newNAME<>@oldNAME)
BEGIN
SET @errMsg='有值,不允许更新!'
GOTO Error_Handle
END
IF(@oldsex<>'' AND @newsex<>@oldsex)
BEGIN
SET @errMsg='有值,不允许更新!'
GOTO Error_Handle
END
SELECT TOP 1 @id = [id],@oldNAME = isnull(NAME,''),@oldsex=ISNULL(sex,'') FROM deleted WHERE [id] > @id ORDER BY [id] ASC
END
RETURN
Error_Handle:
RAISERROR (@errMsg,16,1) WITH NOWAIT
ROLLBACK TRANSACTION
RETURN
END
GO
SELECT * FROM testUPDATE test SET NAME ='22' WHERE id=2
消息 50000,级别 16,状态 1,过程 Trg_Test_Update,第 33 行
有值,不允许更新!
消息 3609,级别 16,状态 1,第 1 行
事务在触发器中结束。批处理已中止。UPDATE test SET sex='3' WHERE id=1
(1 行受影响)
update tb set col='XX' where col is null or Rtrim(col)=''