还是Trigger 问题(95分已经倾尽我所有分数了)(95分)

A，B表构成主从表关系。要实现以下功能：1.当B表中的子记录
不为空时，不允许删除A表中的主记录。2.当A表中的state为1
时，不允许在B表中插入,删除,修改子记录。3.当A表中的state
为1时，不允许删除主记录。
开发工具elphi5+ADO+SQL Server7
问题：
当A表的state为1时,删除A表主记录或在B表中插入,删除子记录
时产生意外错误,不知是为什么?

A表及触发器如下：
CREATE TABLE [dbo].[A] (
[aid] [varchar] (4) NOT NULL ,
[name] [varchar] (8) NULL ,
[state] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TRIGGER [tri_DEL_A] ON dbo.A
FOR DELETE
AS
declare
@aid varchar(4),
@STATE bit

select @STATE=STATE,@AID=AID
from deleted

if @STATE=1
begin
RAISERROR ('TRI_DEL_A:STATE IS TRUE.', 16, 1)
ROLLBACK TRANSACTION
end

IF EXISTS(SELECT * FROM B WHERE AID=@AID)
begin
RAISERROR ('TRI_DEL_A:B IS NOT EMPTY.', 16, 1)
ROLLBACK TRANSACTION
end

GO

*************************************************************
B表及触发器如下：
CREATE TABLE [dbo]. (
[bid] [varchar] (2) NOT NULL ,
[aid] [varchar] (4) NOT NULL ,
[je] [numeric](10, 2) NULL
) ON [PRIMARY]
GO

CREATE TRIGGER [tri_ins_b] ON dbo.B
FOR INSERT
AS
declare
@aid varchar(4),
@STATE bit

select @aid=aid
from inserted

select @STATE=STATE
from A
where aid=@aid

if @STATE=1
begin
RAISERROR ('TRI_INS_B:STATE IS TRUE.', 16, 1)
ROLLBACK TRANSACTION
end

GO

CREATE TRIGGER [tri_DEL_b] ON dbo.B
FOR DELETE
AS
declare
@aid varchar(4),
@STATE bit

select @aid=aid
from DELETED

select @STATE=STATE
from A
where aid=@aid

if @STATE=1
begin
RAISERROR ('TRI_DEL_B:STATE IS TRUE.', 16, 1)
ROLLBACK TRANSACTION
end

GO

CREATE TRIGGER [tri_UPD_b] ON dbo.B
FOR UPDATE
AS
declare
@aid varchar(4),
@STATE bit

select @aid=aid
from inserted

select @STATE=STATE
from A
where aid=@aid

if @STATE=1
begin
RAISERROR ('TRI_UPD_B:STATE IS TRUE.', 16, 1)
ROLLBACK TRANSACTION
end

GO
***********************************************************
Delphi程序如下,即用2个ADOTable,DBGrid分别连接和显视A.B表：
unit Unit1;

interface

uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
ExtCtrls, DBCtrls, Grids, DBGrids, Db, ADODB;

type
TForm1 = class(TForm)
ADOConnection1: TADOConnection;
ADOTable1: TADOTable;
ADOTable2: TADOTable;
DataSource1: TDataSource;
DataSource2: TDataSource;
DBGrid1: TDBGrid;
DBGrid2: TDBGrid;
DBNavigator1: TDBNavigator;
DBNavigator2: TDBNavigator;
private
{ Private declarations }
public
{ Public declarations }
end;

var
Form1: TForm1;

implementation

{$R *.DFM}

end.

观注!

你的Trigger写错了，
select @STATE=STATE,@AID=AID from deleted 只会将DELETED表中的第一条记录选出，
A表的触发器应该这样
if exists (select * from sysobjects where id = object_id(N'[dbo].[Tr_delete_A]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop Trigger Tr_Delete_A
go
Create Trigger Tr_Delete_A on A
For Delete
As
if exists(Select * from deleted where state=1) or
exists(Select * from deleted A,B B where A.Aid:=B.Aid)
begin
raiserror('Error!',16,1)
Rollback
end
SQL SERVER是记录集类型的SERVER，不是单条记录类型的，所以TRIGGER是针对一个记录集合来
写的，这点必须注意
至于B的TRIGGER问题和A的一样，还有可以将TRIGGER写在一起，在写TRIGGER的时候尽量使用JOIN而不要使用IN
if exists (select * from sysobjects where id = object_id(N'[dbo].[Tr_All_B]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop Trigger Tr_All_B
go
Create Trigger Tr_All_B on B
For Delete,Insert,Update
As
if Exists(Select * from deleted B left join A on B.Aid:=A.Aid where A.State=1) or
Exists(Select * from Inserted B left join A on B.Aid:=A.Aid where A.State=1)
begin
raiserror('Error!',16,1)
Rollback
end
怎么样，如果成功快点给分！

关键问题就在误以为deleted,inserted表是一条记录。
例如delete table_a ,insert into table_a select * from table_b产生的deleted,
inserted都很有可能是多条记录集。

还有，如果主副表建了外键做约束，在约束条件通过后触发器才会触发。

zhuhuan兄用你的高招问题已经解决大半，但是还有
一个小毛病：当A表中的state为1时，在B表中删除
子记录，Trigger会提示出错（正常 注意：DBNavigator
的取消按钮是虚的）,确定后选择A表，当选择下一条
记录时又会出现错误提示（不正常）不知是为什么，
怎么解决？(要在Delphi程序中试)
Delphi程序如下,即用2个ADOTable,DBGrid分别连接和显示A.B表：
unit Unit1;

interface

uses
Windows, Messages, SysUtils, Classes, Graphics, Controls,
Forms, Dialogs,ExtCtrls, DBCtrls, Grids, DBGrids, Db, ADODB;

type
TForm1 = class(TForm)
ADOConnection1: TADOConnection;
ADOTable1: TADOTable;
ADOTable2: TADOTable;
DataSource1: TDataSource;
DataSource2: TDataSource;
DBGrid1: TDBGrid;
DBGrid2: TDBGrid;
DBNavigator1: TDBNavigator;
DBNavigator2: TDBNavigator;
private
{ Private declarations }
public
{ Public declarations }
end;

var
Form1: TForm1;

implementation

{$R *.DFM}

end.

是Trigger还是程序的问题？

zhuhuan能解答一下吗？

接受答案了.