create trigger del_five on dbo.employees for delete as if @@rowcount=0 return if @@rowcount>5 begin rollback transaction raiserror('只允许删除五条记录',16,1) end delete from dbo.employees where com_id='c01' --执行它删除了超过5条记录也可以这个是为什么?
稍微改下:create trigger del_five on dbo.employees for delete as begin declare @i int SELECT @I = COUNT(1) FROM DELETED IF (@I = 0) returnif @I>5 begin rollback transaction raiserror('只允许删除五条记录',16,1) end end
create trigger del_five on dbo.employees for delete as if EXISTS(SELECT 1 FROM DELETED Having COUNT(1)>5) begin rollback transaction raiserror('只允许删除五条记录',16,1) end delete from dbo.employees where com_id='c01' --消息 50000,级别 16,状态 1,过程 del_five,第 8 行 --只允许删除五条记录 --消息 3609,级别 16,状态 1,第 1 行 --事务在触发器中结束。批处理已中止。
create trigger del_five on dbo.employees for delete as if @@rowcount=0 return -- print @@rowcount 此处已经为0 if @@rowcount>5 begin rollback transaction raiserror('只允许删除五条记录',16,1) end@@ROWCOUNT 返回受上一语句影响的行数。此处@@rowcount受到比较语句影响
create trigger del_five on dbo.employees for delete as if @@rowcount=0 return if @@rowcount>5 begin rollback transaction raiserror('只允许删除五条记录',16,1) end delete from dbo.employees where com_id='c01' --执行它删除了超过5条记录也可以这个是为什么?你要看deleted表里的记录是多少... select count(*) from deleted
--try: create trigger del_five on dbo.employees for delete asif (select count(1) from deleted)>5 begin rollback transaction raiserror('只允许删除五条记录',16,1) end
--最好是创建删除前触发器create table employees(id int) insert into employees select 1 insert into employees select 2 insert into employees select 3 insert into employees select 4 insert into employees select 5 insert into employees select 6 insert into employees select 7 gocreate trigger del_five on dbo.employees instead of delete as if (select count(1) from deleted)>5 raiserror('只允许删除五条记录',16,1) else delete employees from employees a,deleted b where a.id=b.id godelete employees where id=7 --删除成功delete employees where id>0 --删除失败
稍微改下:create trigger del_five
on dbo.employees
for delete
as
begin
declare @i int
SELECT @I = COUNT(1) FROM DELETED
IF (@I = 0)
returnif @I>5
begin
rollback transaction
raiserror('只允许删除五条记录',16,1)
end
end
on dbo.employees
for delete
as
if EXISTS(SELECT 1 FROM DELETED Having COUNT(1)>5)
begin
rollback transaction
raiserror('只允许删除五条记录',16,1)
end
delete from dbo.employees where com_id='c01' --消息 50000,级别 16,状态 1,过程 del_five,第 8 行
--只允许删除五条记录
--消息 3609,级别 16,状态 1,第 1 行
--事务在触发器中结束。批处理已中止。
on dbo.employees
for delete
as
if @@rowcount=0
return
-- print @@rowcount 此处已经为0
if @@rowcount>5
begin
rollback transaction
raiserror('只允许删除五条记录',16,1)
end@@ROWCOUNT 返回受上一语句影响的行数。此处@@rowcount受到比较语句影响
on dbo.employees
for delete
as
if @@rowcount=0
return
if @@rowcount>5
begin
rollback transaction
raiserror('只允许删除五条记录',16,1)
end
delete from dbo.employees where com_id='c01' --执行它删除了超过5条记录也可以这个是为什么?你要看deleted表里的记录是多少...
select count(*) from deleted
create trigger del_five
on dbo.employees
for delete
asif (select count(1) from deleted)>5
begin
rollback transaction
raiserror('只允许删除五条记录',16,1)
end
--最好是创建删除前触发器create table employees(id int)
insert into employees select 1
insert into employees select 2
insert into employees select 3
insert into employees select 4
insert into employees select 5
insert into employees select 6
insert into employees select 7
gocreate trigger del_five on dbo.employees instead of delete
as
if (select count(1) from deleted)>5
raiserror('只允许删除五条记录',16,1)
else
delete employees from employees a,deleted b where a.id=b.id
godelete employees where id=7
--删除成功delete employees where id>0
--删除失败