OK, I've read it twice and still don't see a question asked

Breck May Be Having A Senior Moment

On 4 Dec 2003 08:29:55 -0800, "Russell J Morgan"
<russell (AT) careervision (DOT) co.uk> wrote:

--
bcarter (AT) risingroad (DOT) com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com

Hi,



Sorry for my ambiguity.



Essentially I was trying to capture the row before it is deleted. I
construct an INSERT Statement around the deleted row, which can be stored
into separate table for deleted transactions as text. This keeps an archive
of any deleted rows. If the customer ever wants to recover the deleted
transaction we can return the record without much effort. I can do all of
this, however I was trying to make my life a lot easier by referencing the
whole row as one variable. This would essentially make my life a lot easier
when in comes to constructing the SQL statement for returning the record if
so desired.



So my question is: Using a trigger, is there a container for holding the
whole row that I may collect and construct an SQL statement around it.



Thanks

No, there isn't. If you were willing to create "shadow tables"
(perhaps with an additional timestamp column added to the primary key)
you could insert the deleted rows in a statement-based after delete
trigger via a simple INSERT SELECT, as in...

INSERT shadow_xxx
SELECT CURRENT TIMESTAMP,
deleted_xxx.*
FROM deleted_xxx;

Another possibility is to ask for the following feature, in the
product_futures_discussion newsgroup: Allow the UNLOAD SELECT
statement to put the resulting formatted string data into a local LONG
VARCHAR variable as well as an external file. Then you could do what
you want with the string.

Breck

On 11 Dec 2003 03:52:21 -0800, "Russell J Morgan"
<russell (AT) careervision (DOT) co.uk> wrote:

--
bcarter (AT) risingroad (DOT) com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com

ASA7.04 win32



Hello,



I have managed to store the deleted record in CSV format in a column long
binary, so that all deleted records can be recovered at any chosen moment. I
'm now working on the recovery procedures. The problem is at the minute,
unfortunately I have to dump out to disk to be able to read it back in. I
suppose that's ok, but it would be nice just to collect the field, and read
it back in without going to the disk.



My current approach: (The field transaction contains the CSV)



Export the contents of "transaction" to d:\data.txt and insert into table1.



Select xp_write_file('d:\data.txt', "transaction") from archive_data where
Pkey = 10;

INPUT INTO TABLE1

from d:\data.txt

format ascii;



Now, with a little formatting on the field I can read the column containing
CSV into a

Variable and all I need to do then is insert it.this is where I'm stuck



Populate SQLVar with the contents of "transaction" stripping off the end of
line characters:



CREATE VARIABLE SQLVar Long Varchar;

SET SQLVar = (Select LEFT("Transaction", LENGTH("Transaction") -2) AS
"Transaction" from archive_data where "pkey" = 5);



--fails here

INSERT INTO TABLE1 VALUES(SQLVar);



--Looks like : INSERT INTO TABLE1 VALUES(1734,'7','Y',,,)

DROP VARIABLE SQLVar;



For the insert to work all the columns need populating where there are null
values.



Any ideas how I can do this



TIA



Russell J Morgan