What's wrong with this trigger

[Jeff via AccessMonster.com]

When I run the trigger below I get the following message:

"the data was added to the database but won't be displayed in the form
because it doesn't satisfy the criteria in the underlying record source."

The funny thing is I'm not even in a form. This message appears when I try
to manually insert a new record at the table level using an ADP. The Record
is still inserted and the trigger is working but what's up with the message?
???

Here's the code:

CREATE TRIGGER [NewIndividual] ON Individuals
FOR INSERT
AS
INSERT [Individuals_Audit] (...)
SELECT ...
FROM inserted

At the form level when I try to insert a record I get a message indicating
can't find the field 'Forms' referred to in your expression.

I don't think it's my fields because I have a similar Update trigger that
works fine. It has something to do with inserting a new record into my
audit table.

 

[Malcolm Cook]

Jeff,

your audit table probably has a IDENTITY on it
the INSERT in your trigger side effects @@IDENTITY
this confuses the ADP

the solution/workaround : in your trigger, cache @@IDENTITY before doing the
INSERT and restore it afterwards

see a previous thread on this where I give solution:
http://groups-beta.google.com/group...erver/msg/2f5ce86f0fe13b0a?dmode=source&hl=en

Good luck,

 

[Sylvain Lafontaine]

If you are removing the insert trigger, are you still seeing the same error
message?

 

[Jeff via AccessMonster.com]

It only occurs when the tigger is enables. I can't seem to get the EXECUTE
command to work though.

EXECUTE (N'SELECT Identity (Int, ' + Cast(@myID As Varchar(10)) + ' , 1) AS
id
INTO #Tmp'

What exactly does this do?

 

[Vadim Rapp]

Hello Jeff:
You wrote in conference microsoft.public.access.adp.sqlserver on Fri, 29
Apr 2005 13:33:26 GMT:


JvA> Here's the code:

JvA> CREATE TRIGGER [NewIndividual] ON Individuals
JvA> FOR INSERT
JvA> AS
JvA> INSERT [Individuals_Audit] (...)
JvA> SELECT ...
JvA> FROM inserted

please post the schema for the tables.

thanks,

Vadim Rapp

 

[Guest]

I have a problem that I think might be related to this, where I have an
update trigger that inserts an audit row into a view. It works fine from
SQL Server Enterprise Manager, but when I go into the table through the ADP,
it gives me the error 'Key column information is insufficient or incorrect.
Too many rows were affected by update.' I tried the trick of SET NOCOUNT
OFF and caching the identity, however I am still receiving the same error.
Not sure what else to try! The primary key is in fact an identity and so is
the view that I'm inserting the audit row into.

Any help you can provide would be greatly appreciated!
MH

 

[Guest]

I meant to say "SET NOCOUNT ON"...

I have a problem that I think might be related to this, where I have an
update trigger that inserts an audit row into a view. It works fine from
SQL Server Enterprise Manager, but when I go into the table through the
ADP, it gives me the error 'Key column information is insufficient or
incorrect. Too many rows were affected by update.' I tried the trick of
SET NOCOUNT OFF and caching the identity, however I am still receiving the
same error. Not sure what else to try! The primary key is in fact an
identity and so is the view that I'm inserting the audit row into.

Any help you can provide would be greatly appreciated!
MH

 

[Jeff via AccessMonster.com]

After reviewing what the previous datbase developer did with the audit
tables I realized that there was no need for an identity field in the audit
tables. Eveything works fine now that I removed the identity fields.

I'm still learning all this on the fly as most of my experience is with
Access on it's own.

On a seperate topic, one thing in Access which was possible was to build a
query based on other queries. Is this possible with stored procedures
without creating Temp tables?

 

[Guest]

The exact syntax I used to store the identity field is the following:

DECLARE @tbl_id smallint
SET @tbl_id = @@IDENTITY

SET NOCOUNT OFF

-- main logic inserted here using an insert into a # table (no identity
column) and after manipulating the # table, the results are inserted into
the view (which does have an identity). The table that actually contains
the trigger is only used as a join for the resultset that is brought across.

SET NOCOUNT ON

DECLARE @sql varchar(100)
SET @sql = 'SELECT Identity (int, ' + CAST(@tbl_id AS varchar(10)) + ',1) AS
id INTO #Tmp'
EXECUTE (@sql)

 

[Guest]

You can do a subselect that would be joined to another table. Or you can
create a view and select from that.

The subselect works as follows:

select <fields you need>
from table1 t1
inner join (select * from table2) t2 on t1.id = t2.id

 

[Sylvain Lafontaine]

You are right, on many occasions (or designs), audit tables doesn't require
their own identiy field.

For your question about queries, SP returns RowSets and not tables or views,
so you cannot use them directly inside a Select query but you can convert
them back to tables by using the instruction OpenRowSet, OPENDATASOURCE et
OPENQUERY.

However, easier solutions would be to use User Defined Functions (UDF)
returning table variables or to use subqueries. The level and complexity of
subqueries that you can write with T-SQL is much greater than with Access.

 

[Sylvain Lafontaine]

Yes, insert triggers that change the @@identity value cause many trouble
with ADP. See
http://support.microsoft.com/default.aspx?scid=kb;EN-US;275090 for example.

As suggested by Jeff in another posting, the easiest solution is to remove
the identity value from the historical tables; for example by using the same
values for the primary key as the ones used in the original tables. Another
solution would be to use your own stored procedure to create the insert and
then resynchronise the form.

 

[Guest]

I don't have that option in this case. The purpose of the downstream tables
is to update another application with changes that were made. The table
that has the trigger is control data, so the results that are moved over to
the changes table are not the records that are in the table, but records
that are affected by changing the table. I use an identity column to have
the records in sequential order of when they were created, so the
application that picks up the changes can get the latest change. I don't
understand why capturing the identity as Malcolm suggested, doesn't work.
The solution he had, had a typo, because there was no closing parenthesis,
but I fixed that and still have the same error. If it was up to me, I would
scrap the Access app at this point, but I don't have time for the redesign
as of yet. The ONLY reason I'm having this issue is because of Access, and
the app that uses this data is .NET. It's very frustrating, especially
since a legacy app is preventing me from moving forward.

Yes, insert triggers that change the @@identity value cause many trouble
with ADP. See
http://support.microsoft.com/default.aspx?scid=kb;EN-US;275090 for
example.

As suggested by Jeff in another posting, the easiest solution is to remove
the identity value from the historical tables; for example by using the
same values for the primary key as the ones used in the original tables.
Another solution would be to use your own stored procedure to create the
insert and then resynchronise the form.

 

[Guest]

Note also that my issue is not quite the same as I'm not getting the same
error, but I think the underlying cause could be similar. My error is 'Key
column information is insufficient or incorrect. Too many rows were affected
by update.'

 

[Sylvain Lafontaine]

By using the profiler on SQL-Server, you should see exactly what Access is
trying to do and how to solve it.

..NET has been designed expressively to solve this kind of problem; so it's
no wonder that you don't have this problem with your .NET applications.

 

[Guest]

Ok, never mind about all that. I actually had SET NOCOUNT OFF at the top,
instead of SET NOCOUNT ON. *BLUSH* It is working now, without the identity
workaround, even though both the triggered table and the table underlying
the view have identities.

 

[wuyouren]

ja od f f

 

[wuyouren]

??????