急求触发器解答

[idot]

我刚开始用触发器，请向大家请教一个问题：我写了一个触发器如下：
CREATE TRIGGER [forbidToDeleteCompany] ON [dbo].[Company]
FOR DELETE
AS
begin

declare @tableName varchar(255)
declare @fieldName varchar(255)
declare @tcount int
DECLARE tableFieldCursor CURSOR FOR
select t.[name] as tableName,f.[name] as fieldName
from sysobjects as t
inner join syscolumns as f
on t.id = f.id
where t.type='U' and lower(f.[name]) like '%companycode'

OPEN tableFieldCursor
FETCH NEXT FROM tableFieldCursor
INTO @tableName, @fieldName
WHILE @@FETCH_STATUS = 0
BEGIN
exec('select @tcount = count(d.code) from deleted as d inner join '+ @tableName +' as p on d.code=p.' + @fieldName)
if @tcount >0
RAISERROR ('记录在使用中不能删除！', 16, 1)
rollback transaction
goto quit

FETCH NEXT FROM tableFieldCursor
INTO @tableName, @fieldName

END

quit:
CLOSE tableFieldCursor
DEALLOCATE tableFieldCursor

end

但是在删除记录时，触发器出错，提示“必须声明变量@tcount”，经查是exec语句出错，但不知道该如何写才对，请大家不吝赐教。

[rookie_one]

exec('select @tcount = count(d.code) from deleted as d inner join '+ @tableName +' as p on d.code=p.' + @fieldName)

直接写试试看
select @tcount = count(d.code) from deleted as d inner join ''+ @tableName +'' as p on d.code=p.'['+@fieldName+']'

[中国风]

用法不正确:
CREATE TRIGGER [forbidToDeleteCompany] ON [dbo].[Company]
FOR DELETE
AS
begin

declare @tableName varchar(255)

declare @tcount int,@fieldName varchar(255),@tableName sysname,@sql nvarchar(1000)
DECLARE tableFieldCursor CURSOR FOR
select t.[name] as tableName,f.[name] as fieldName
from sysobjects as t
inner join syscolumns as f
on t.id = f.id
where t.type='U' and lower(f.[name]) like '%companycode'

OPEN tableFieldCursor
FETCH NEXT FROM tableFieldCursor
INTO @tableName, @fieldName
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql=N'select @tcount = count(d.code)
from deleted as d inner join '+ @tableName +' as p on d.code=p.' + @fieldName
exec sp_executesql @sql,N'@tcount int output',@tcount output
if @tcount >0
RAISERROR ('记录在使用中不能删除！', 16, 1)
rollback transaction
goto quit

FETCH NEXT FROM tableFieldCursor
INTO @tableName, @fieldName

END

quit:
CLOSE tableFieldCursor
DEALLOCATE tableFieldCursor

end

[中国风]

楼主用以上语句修改后试试

[idot]

roy_88(中国风_燃烧你的激情！！！) :
您好，您的代码没有语法错误，但是在删除记录时提示“对象名deleted无效”，能帮我解决不，在下不胜感激！

[zhang_yzy]

貌似无法解决，
deleted表在EXEC中，可能不被认可

[zhang_yzy]

看了你的程序，个人感觉解决起来，很麻烦！
要不你在中间在加一个游标吧
CREATE TRIGGER [forbidToDeleteCompany] ON [dbo].[Company]
FOR DELETE
AS
begin

declare @tableName varchar(255)

declare @tcount int,@fieldName varchar(255),@tableName sysname,@sql nvarchar(1000)
DECLARE tableFieldCursor CURSOR FOR
select t.[name] as tableName,f.[name] as fieldName
from sysobjects as t
inner join syscolumns as f
on t.id = f.id
where t.type='U' and lower(f.[name]) like '%companycode'

declare @code int--具体的类型自己定
DECLARE deleteFieldCursor CURSOR FOR
select code
from deleted
group code

OPEN tableFieldCursor
FETCH NEXT FROM tableFieldCursor
INTO @tableName, @fieldName


WHILE @@FETCH_STATUS = 0
BEGIN
open deleteFieldCursor
fetch first from deleteFieldCursor
into @code

while @@fetch_status =0
begin
set @sql=N'select @tcount = count(*) '+
' from '+ @tableName +
' where ' + @fieldName +'='+@code
exec sp_executesql @sql,N'@tcount int output',@tcount output
if @tcount >0
begin
RAISERROR ('记录在使用中不能删除！', 16, 1)
rollback transaction
goto quit
end
fetch next from deleteFieldCursor
into @code
end


FETCH NEXT FROM tableFieldCursor
INTO @tableName, @fieldName

END

quit:
close deleteFieldCursor
deallocate deleteFieldCursor
CLOSE tableFieldCursor
DEALLOCATE tableFieldCursor

end
粗略的写了一下，你自己在根据情况改吧

[idot]

zhang_yzy(六子儿) ：
您好，谢谢您的点拨，我通过如下方法而解决，
先执行 select * into #deletedCompanies from deleted
然后把exec中的deleted替换为 #deletedCompanies