primary key value in sqla vs sql server

[Former Member]

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

[VolkerBarth]

How are the different tables in SQL Anywhere and MS SQL Server declared? Are you using DEFAULT AUTOINCREMENT vs. IDENTITY PKs?

[Former Member]

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?

[Former Member]

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?

[VolkerBarth]

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.

[Former Member]

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.