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

asked 02 Oct '12, 11:32

javedrehman's gravatar image

javedrehman
256141421
accept rate: 0%

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

(02 Oct '12, 14:53) Volker Barth

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?

(02 Oct '12, 15:29) javedrehman

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?

(02 Oct '12, 15:30) javedrehman
Replies hidden

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.

(02 Oct '12, 15:37) Volker Barth

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.

(02 Oct '12, 16:05) javedrehman
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • 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:

×4

question asked: 02 Oct '12, 11:32

question was seen: 801 times

last updated: 02 Oct '12, 16:05