Trigger error does not surface

[J. M. De Moor]

MS Access 2000 (SP-3) using ADP with unbound forms
SQL Server 2000 (SP3)
MDAC 2.6

I have an INSTEAD OF INSERT trigger attached to an updateable view (WITH
VIEW_METADATA). There are 2 INSERT statements in this trigger to update the
underlying base tables. If both work, everything is OK. If the first SQL
statement fails, the error code is returned to the ADP and the On Error
event fires, which is also what I want. However, if the first SQL statement
succeeds and the second SQL statement fails, the On Error event in the ADP
does NOT fire, evidently because the first SQL statement succeeds.
Consequently, there is no evidence in the application that the trigger
failed to update the tables.

CREATE TABLE dbo.Foo (
key_col CHAR(1) NOT NULL PRIMARY KEY
,col_1 VARCHAR(5) NOT NULL
,col_2 VARCHAR(20) NOT NULL
);

CREATE TABLE dbo.Bar (
key_col CHAR(1) NOT NULL
REFERENCES Foo (key_col)
ON UPDATE CASCADE
ON DELETE CASCADE
,seq INT NOT NULL
,col_3 CHAR(1) NOT NULL
CHECK (col_3 IN ('Y', 'N'))
,PRIMARY KEY (key_col, seq)
);

GO

CREATE VIEW dbo.Foobar
WITH VIEW_METADATA
AS
SELECT
F.key_col
,F.col_1
,F.col_2
,B.seq
,B.col_3
FROM Foo AS F
LEFT OUTER JOIN Bar AS B ON B.key_col = F.key_col

GO

CREATE TRIGGER Foobar_ins ON Foobar
INSTEAD OF INSERT
AS
BEGIN
IF @@ROWCOUNT = 0 RETURN
INSERT INTO Foo
SELECT key_col, col_1, col_2 FROM Inserted;
INSERT INTO Bar
SELECT key_col, seq, col_3 FROM Inserted
WHERE seq IS NOT NULL;
END

GO

The following statement in the CommandText property of an ADODB.Command will
not fire an On Error event in the ADP even though SQL identifies a
constraint error on col_3:

INSERT INTO Foobar VALUES ('A', 'AAAAA', 'AA AAA AAA', 1, 'L');

I have tried saving the @@ERROR in the trigger but the trigger appears to
halt before getting to that statement. (This goes against all the
documentation and 3rd party books I have read. When I execute an insert on
the view in Query Analyzer, it halts the trigger at the failed SQL statement
and does not give me a chance to do anything with the error code.) Is there
an SQL setting or something else I am missing?

Thanks,
Joe

 

[giorgio rancati]

Hi,

insert *SET NOCOUNT ON* in the trigger
----
CREATE TRIGGER Foobar_ins ON Foobar
INSTEAD OF INSERT
AS
BEGIN
IF @@ROWCOUNT = 0 RETURN
--in this position
SET NOCOUNT ON
INSERT INTO Foo
SELECT key_col, col_1, col_2 FROM Inserted;
INSERT INTO Bar
SELECT key_col, seq, col_3 FROM Inserted
WHERE seq IS NOT NULL;
END