trigger blues no primary key

Question

trigger blues no primary key

fizzleme

SSCertifiable

Points: 6906
More actions
December 27, 2018 at 11:12 am

#381100

I'm converting a database to sql server from sql anywhere. The tables use a trigger to update a column that has a timestamp. I'm using a cursor to scan the inserted table, capture the primary keys and update the production table. The tables are small so the cursor is acceptable. This works fine except for those few tables without primary keys. I'm considering referencing non key columns in the inserted table and using those to try to match the production table. This is not perfect but I can't think of another way to do this. Alternatives?
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

fizzleme - Thursday, December 27, 2018 11:12 AM
I'm converting a database to sql server from sql anywhere. The tables use a trigger to update a column that has a timestamp. I'm using a cursor to scan the inserted table, capture the primary keys and update the production table. The tables are small so the cursor is acceptable. This works fine except for those few tables without primary keys. I'm considering referencing non key columns in the inserted table and using those to try to match the production table. This is not perfect but I can't think of another way to do this. Alternatives?

Based on your description, I'd still say that the cursor is not acceptable.

Does the table contain one or more columns which uniquely identify a single row?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

fizzleme - Thursday, December 27, 2018 11:12 AM
The tables are small so the cursor is acceptable.

No, it's actually not. 😉 I hope you don't find that out the hard way.

As for the tables with no Primary Key, if there's absolutely nothing to uniquely identify the row, consider using INSTEAD OF triggers... and not using cursors in the triggers. They're a bit of a pain in the butt so it would be worthwhile to add an IDENTITY column to the tables even if the only thing you used it for was to do your trigger-driven updates in an efficient manner.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 3

It's hard to know what else you can do without seeing some code and data. I would agree a cursor is not needed. You ought to be able to update the table directly from inserted without a cursor.

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 4

fizzleme - Thursday, December 27, 2018 11:12 AM
I'm converting a database to SQL server from SQL Anywhere. The tables use a trigger to update a column that has a timestamp. I'm using a cursor to scan the inserted table, capture the primary keys and update the production table. The tables are small so the cursor is acceptable. This works fine except for those few tables without primary keys. I'm considering referencing non-key columns in the inserted table and using those to try to match the production table. This is not perfect but I can't think of another way to do this. Alternatives?

If it does not have a key, then it is not a table, by definition. You can also use a DEFAULT CURRENT_TIMESTAMP clause on this column ...

Please post DDL and follow ANSI/ISO standards when asking for help.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

jcelko212 32090 - Sunday, December 30, 2018 3:59 PM
fizzleme - Thursday, December 27, 2018 11:12 AM
I'm converting a database to SQL server from SQL Anywhere. The tables use a trigger to update a column that has a timestamp. I'm using a cursor to scan the inserted table, capture the primary keys and update the production table. The tables are small so the cursor is acceptable. This works fine except for those few tables without primary keys. I'm considering referencing non-key columns in the inserted table and using those to try to match the production table. This is not perfect but I can't think of another way to do this. Alternatives?
If it does not have a key, then it is not a table, by definition. You can also use a DEFAULT CURRENT_TIMESTAMP clause on this column ...

Amen to both!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)