create trigger tr_b on b for delete as declare @id int set @id=0 select @id=1 from deleted where id=999 if(@id=1) rollback tran
create trigger my_trig on tb for delete begin if exists(select 1 from deleted where id = 999) insert into tb select * from deleted where id = 999 end
USE TEMPDB GO IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB GO CREATE TABLE TB( ID INT ,VAL VARCHAR(50) ) INSERT INTO TB SELECT 1,'A' UNION ALL SELECT 2,'B' UNION ALL SELECT 3,'C' GO --第一种方法 CREATE TRIGGER TIG_TB_1 ON TB FOR DELETE AS BEGIN IF EXISTS( SELECT 1 FROM DELETED WHERE ID=3 ) BEGIN PRINT '3不能删' ROLLBACK TRAN END --ELSE --DELETE FROM TB WHERE EXISTS( --SELECT 1 FROM DELETED WHERE DELETED.ID=TB.ID --) END GO --第二种方法 CREATE TRIGGER TIG_TB_2 ON TB INSTEAD OF DELETE AS BEGIN IF EXISTS( SELECT 1 FROM DELETED WHERE ID=3 ) BEGIN PRINT '3不能删' END ELSE DELETE FROM TB WHERE EXISTS( SELECT 1 FROM DELETED WHERE DELETED.ID=TB.ID ) END GO DELETE FROM TB WHERE ID=3
create trigger tr_b on b
for delete
as
declare @id int
set @id=0
select @id=1 from deleted where id=999
if(@id=1)
rollback tran
begin
if exists(select 1 from deleted where id = 999)
insert into tb select * from deleted where id = 999
end
GO
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(
ID INT
,VAL VARCHAR(50)
)
INSERT INTO TB
SELECT 1,'A' UNION ALL
SELECT 2,'B' UNION ALL
SELECT 3,'C'
GO
--第一种方法
CREATE TRIGGER TIG_TB_1
ON TB
FOR DELETE
AS
BEGIN
IF EXISTS(
SELECT 1 FROM DELETED WHERE ID=3
)
BEGIN
PRINT '3不能删'
ROLLBACK TRAN
END
--ELSE
--DELETE FROM TB WHERE EXISTS(
--SELECT 1 FROM DELETED WHERE DELETED.ID=TB.ID
--)
END
GO
--第二种方法
CREATE TRIGGER TIG_TB_2
ON TB
INSTEAD OF DELETE
AS
BEGIN
IF EXISTS(
SELECT 1 FROM DELETED WHERE ID=3
)
BEGIN
PRINT '3不能删'
END
ELSE
DELETE FROM TB WHERE EXISTS(
SELECT 1 FROM DELETED WHERE DELETED.ID=TB.ID
)
END
GO
DELETE FROM TB WHERE ID=3