 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / SQL Server / ADP / May 2005What's wrong with this trigger
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. 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/microsoft.public.access.adp.sqlserver/msg/2f 5ce86f0fe13b0a?dmode=source&hl=en Good luck,  SignatureMalcolm Cook - mec@stowers-institute.org Database Applications Manager - Bioinformatics Stowers Institute for Medical Research - Kansas City, MO USA > When I run the trigger below I get the following message: > [quoted text clipped - 21 lines] > works fine. It has something to do with inserting a new record into my > audit table. If you are removing the insert trigger, are you still seeing the same error message? SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC > When I run the trigger below I get the following message: > [quoted text clipped - 23 lines] > works fine. It has something to do with inserting a new record into my > audit table. 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? 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 > It only occurs when the tigger is enables. I can't seem to get the EXECUTE > command to work though. [quoted text clipped - 5 lines] > > What exactly does this do? 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 [quoted text clipped - 18 lines] >> >> What exactly does this do? 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) >I meant to say "SET NOCOUNT ON"... > [quoted text clipped - 20 lines] >>> >>> What exactly does this do? 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. SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC >I meant to say "SET NOCOUNT ON"... > [quoted text clipped - 20 lines] >>> >>> What exactly does this do? 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 [quoted text clipped - 31 lines] >>>> >>>> What exactly does this do? 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.' >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 [quoted text clipped - 47 lines] >>>>> >>>>> What exactly does this do? 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. SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC > 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 [quoted text clipped - 52 lines] >>>>>> >>>>>> What exactly does this do? 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. > 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 [quoted text clipped - 52 lines] >>>>>> >>>>>> What exactly does this do? ja od f f 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 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? 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 > 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 [quoted text clipped - 7 lines] > query based on other queries. Is this possible with stored procedures > without creating Temp tables? 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. SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC > 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 [quoted text clipped - 7 lines] > query based on other queries. Is this possible with stored procedures > without creating Temp tables? ?????? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.