Hi all,

I have a simple SQL Trigger on item master (tiitm001) which fires on every update to the item master table. The pupose of this trigger is to capture the old and new value of the average purchase price. These values I'm writing into a table in another database.

Sys. configuration is BaaN IVc4 using MSSQL 7 database on NT4


====================================

CREATE TRIGGER ItemMasterTrig
ON ttiitm001300
FOR UPDATE
AS
BEGIN

UPDATE Shopfloor.dbo.Test
Set Counter = (Select Counter From Shopfloor.dbo.Test where Name ='A')+1,
OldData = (select t_avpr From deleted),
NewData = (select t_avpr From inserted)
WHERE Name = 'A'
END

========================================

This trigger works well, but the problem is that the baan session (tiitm0101m000 - Maintain Item Data) blows up with error 1000 (anybody know what this error means?).

Is it not possible to use triggers in baan database without killing off your baan sessions?

The interesting bit is that the trigger works well without harming the item master session if one has changed only fields such as search key etc, but a change in purchase price would crash the session.

Thanks
joe

I am pretty sure we were advised not to put triggers on Baan tables. Although we do use stored procedures. I will check with somebody here to see exactly what we do & let you know if I think it would be useful to you.

I would suspect that the reason it falls over if you change the purchase price (& not the search key) is because in Baan changing the purchase price may initiate other fields & tables to be changed - example "average purchase price" would get changed. Changing the search key won't change any other field or table.

MS SQL Server error: Restricted data type attribute violation
Author B Rombouts Creation Date: 01 Jun 2001 Alternate ID:
Solution No: 115214 Last Modified: 06 Jun 2001 Status: Published
Product: ttB50b Sub Product: Session: msql7_srv.exe
Package: tt Version: B50 Release: b
Solution Type:

Symptom information
Error 850
no access to the table tiitm001 possible

Solution description
VERSION(S):
All

SITUATION IDENTIFIED IN:
SQL Server driver (msql7_srv.exe)

SITUATION DESCRIPTION:
After installing some solutions, we receive the next error when executing a session:
Read Row Error 1000 on tfgld004100
Cannot continue tdsls4122m000

In the NT event viewer the next error messages are logged:

dbs_errno = 1000 Error 1000 occurred:
Error sqlcode 0 (sqlstate: 07006, Return code: -1, diagRecNum: 1) during SQLFetch.
[Microsoft][ODBC SQL Server Driver]Restricted data type attribute violation
Flushed at \db\servers\MSQL\msql_error.c : #574.

dbs_errno = 1000 Error 1000 occurred:
Error during fetch read (dbo.ttfgld004100, QRY_EQUAL).
Flushed at \db\servers\MSQL\msql_row.c : #261.

Error 1000 (bdb_errno 1000) on SELECT


SOLUTION DESCRIPTION:
The problem is caused by the installation of new table definitions.

By default, SQL server uses Stored Procedures to execute specific queries.
Because some table fields have changed in Baan, the SQL Server tables have changed also, but the Stored

Procedures are not updated with the new definition.

There are three ways to solve this problem:

* Disable the usage of using Stored Procedures:
This can be done by adding the next variable to the file %BSE%\lib\defaults\db_resource:
msql_use_sp:0
Advantage: It is a quick solution and the problem will never occur again.
Disadvantage: The performance of some specific queries will slightly decrease.

* Remove all Stored Procedures from SQL Server that are used by Baan:
This can be done through SQL Server Enterprise Manager:
- In the menu tree select "databases\<database_name>\stored procedures"
- Select all Stored Procedures that are generated by Baan. They all have a name like

P<tablename><companynumber>_<index_number>. For example: "Ptccom000550_1"
- Delete the selected Stored Procedures
Advantage: It is a quick solution to the problem and solves all possible problems due to the new Baan table

definitions.
Disadvantage: All Stored Procedures will automatically be regenerated when the table will be queried again for

the first time. When new solutions are installed, the problem could reoccur.

* Remove the Stored Procedure from SQL Server that caused the error:
This can be done through SQL Server Enterprise Manager:
- In the event viewer, look for the table name that caused the problem. If it cannot be found here, you need to

trace the application with DBSLOG=470. In the resulting trace file the wrong stored procedure will be mentioned.
- In the menu tree select "databases\<database_name>\stored procedures"
- Select Stored Procedures that belong to the mentioned table. They have a name like

P<tablename><companynumber>_<index_number>. For example: "Ptccom000550_1"
- Delete the selected Stored Procedures
Advantage: Performance will not decrease, because only one Stored Procedure is deleted.
Disadvantage: Other tables can still suffer from this problem. The problem can reoccur.

EXAMPLE(S):
Not Applicable.

WORK-AROUND:
Not Applicable..

----------


Fatal Error 1000 on select cannot continue in tccom130
Author F Eijgelaar Creation Date: 10 Jan 2000 Alternate ID:
Solution No: 79296 Last Modified: 15 Jan 2001 Status: Published
Product: whB50b Sub Product: Session: tccom4530m000
Package: wh Version: B50 Release: b
Solution Type: KR:Question

Solution description
VERSION(S):
B50b

SITUATION IDENTIFIED IN:
Addresses (tccom4530m000)

SITUATION DESCRIPTION:
Fatal error 1000(bdb_error 1000) read row error on select cannot continue in tccom130001 (error 0.get.company)
Error in MSSQLl7_SRV "Restricted data type attribute violation"

SOLUTION DESCRIPTION:
The problem is related to a corrupt Stored_Procedure for the SQL-query.
To find the correct procedure that is causing the "Restricted data type attribute violation", it is neccessary to

create a DBSlog. Based on this log the corrupted procedure with
code Pttccom130001_1 can be identified which is used by the query for the Baandb.
After this corrupted procedure has been removed it is possible again to use the change option again in the Table

Maintenance.
Once using an index on the table the new procedure is automatically created again.

The Stored_procedures can be found by taking the following steps:
- Go to the SQL Server Enterprise Manager, select the specific servername.
- Select Databases,
- Select the default database (often baandb).
- Select the Stored Procedures.

EXAMPLE(S):
Not Applicable.

WORK-AROUND:
Not Applicable.

We tried your trigger on tiitm001 on our Win 2000/SQL 2000 system and as soon as we tried to save a purchase price changed record we got a db error.
The error on our system was dbs_errno 1916 event 1000, but I think the 1916 refers to the fact that our db user may not have permissions to copy the purchase price into the other database.

Hi Brendan Shine & EdHubbard,

Thanks Brendan Shine for the informative articles on error 1000. And EdHubbard thanks for trying out the trigger at your end, once you give yourself the permission for the database you are writtting into, then the only error that will pop up is error 1000.

I plan to test by removing the temp stored procedure. I'll keep you posted. Please let me know if there is anything more I need to know about Baan and triggers, or about error 1000.


Thanks,
joe