|
I have a datawindow that must work with both sqla and sql server for one of our apps. the table datawindow is attached to in both dbms has a primary key unique column. When I insert a new row the driver or the database in sqla passes a value that is not already in the table but when same insert is done against sql server it passes value 1 which is already in the table and hence the insert fails. I know this may be a question related to sql server and powerbuilder but I couldn't find any answers anywhere so i thought I will ask it here and may be someone had experienced it before. thanks |
Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.
Follow this question
By Email:Once you sign in you will be able to subscribe for any updates here
By RSS:Markdown Basics
- *italic* or _italic_
- **bold** or __bold__
- link:[text](http://url.com/ "title")
- image?
- numbered list: 1. Foo 2. Bar
- to add a line break simply add two spaces to where you would like the new line to be.
- basic HTML tags are also supported
Question tags:
question asked: 02 Oct '12, 11:32
question was seen: 2,077 times
last updated: 02 Oct '12, 16:05
How are the different tables in SQL Anywhere and MS SQL Server declared? Are you using DEFAULT AUTOINCREMENT vs. IDENTITY PKs?
key in both databases, no autoincrement no identity pk or anything however I did just discovered that sqla had a trigger on this table which is declared something like this:
TRIGGER "tr_logon_before_insert" before insert on DBA.logon
this trigger actually sets the value for the column i m having problem with in sql server. It seems like there is nothing in powerbuilder that gets done, powerbuilder simply issues an insert statement but sqla beause of this trigger goes and sets the value for the problem column before insert takes place but sql server either does not have this trigger or logic is not working. Is my assumption for beofre insert trigger in sqla correct?
sorry the previous comment was supposed to be:
Volker: the primary key column that is causing me problem in sql server is defined just as unique key in both databases, no autoincrement no identity pk or anything however I did just discovered that sqla had a trigger on this table which is declared something like this:
TRIGGER "tr_logon_before_insert" before insert on DBA.logon
this trigger actually sets the value for the column i m having problem with in sql server. It seems like there is nothing in powerbuilder that gets done, powerbuilder simply issues an insert statement but sqla beause of this trigger goes and sets the value for the problem column before insert takes place but sql server either does not have this trigger or logic is not working. Is my assumption for beofre insert trigger in sqla correct?
Well, you don't have shown the trigger code, but it is surely possible to use a BEFORE INSERT trigger to calculate a PK value before the row is stored. (Whether a DW will show that value is out of my knowledge, as it requires PB to requery the row's contents, but obviously it does so.)
On the other hand, MS SQL Server does not support BEFORE triggers and requires PK values to be set beforehand, so you will have to use other methods to automatically generate such values - and IDENTITY() is the preferred one, methinks.
volker u r correct sir: regardless of whether datawindow shows it or not before insert trgr is working in sqla. I found out that we do have a trgr in sql server that is declared as 'instead of insert' I have never used it b4, supposedly it will do the samething as before insert but obviously either the logic in it is not working or it simply does not work, but now I know where the problem is and there are few options available to tackle this problem. thank you very much.