What I'm trying to do is the following: I'm synchronizing data from 1 database to another database using proxy tables. Because sybase doesn't allow me to update proxy tables currently I'm deleting the record and then add the record again with the new info. When the record is deleted also some other records are deleted in other tables because I don't know what columns exactly have been changed. Now the customer only wants the records to be deleted when some columns are changed. I'm trying to achieve this like this:
I want the check to use the SysColumns table do be able to dynamicly check the data for every column in the 'Table' record. To create the tables and data into the tables use: BEGIN
DROP TABLE Tank; BEGIN
DROP TABLE TankChanges; BEGIN
DROP PROCEDURE TankChangedColumns; CREATE TABLE Tank ( CREATE TABLE TankChanges ( INSERT Tank VALUES ( 1, 1, 1 ); I tried to achieve this with the following procedure: create procedure TankChangedColumns(in in_TankId integer) But the procedure stops after checking the first column. Can anyone help? Regards, Frank Vestjens Comment Text Removed
|
I would suggest a solution without using a cursor. You can use the set-operators EXCEPT and INTERSECT to find those rows that have (not) been changed between both tables, i.e.
will show identical rows, whereas
will list those rows that have been altered (or inserted) in TankChanges. If you are only interested in changes of particular columns, you could generate the according SELECT-list based on this column list (maybe by means of EXECUTE IMMEDIATE). If you want to have different actions based on whether the differing rows are in this list you might combine both actions with the help of a MERGE statement. |