creating temp sequences

[Olaf Doschke]

SQL Server answers my need of temp sequences with NO, i.e. CREATE SEQUENCE #counter START WITH 1 INCREMENT BY 1 ; is not working, sequence names are not allowed to start with #
Any way to overcome this? CREATE SEQUENCE tempdb.counter START WITH 1 INCREMENT BY 1 won't work, too.

The more general problem is a bit long winded.

A related question would be, can I iterate the records in the table variables "inserted" and "deleted" of an update trigger, so I could process one record of deleted, then one of inserted, then next of deleted, next of inserted, etc., interleaved? Please don't suggest an inner join by IDs, the problem is records may alter in every field, including an id field or tables may not have a primary key. I try to cover that general case, at least.

Bye, Olaf.

 

[bborissov]

How about to declare table variable based on the structure you need and
add one additional field which will show you from what table you are get records.
Then insert records from INSERTED and DELETED in it?
After that you can loop through all records of that table:

DELCARE @Test TABLE (Fld1 int, Fld2 varchar(20), RowN I, FladTable bit)
INSERT INTO Test (Fld1, Fld2, RowN, FladTable)
SELECT Fld1, FLd2, ROW_NUMBER() OVER (ORDER BY ????), 0
FROM Deleted
UNION ALL
SELECT Fld1, FLd2, ROW_NUMBER() OVER (ORDER BY ????), 1
FROM INSERTED
...

Borislav Borissov
VFP9 SP2, SQL Server

 

[Olaf Doschke]

Thanks,

that table exists anyway, the destination of all the deleted and inserted records, but I still have another idea based on timestnmp. I add a timestamp field to the @history log table and can sort by that, skipping forward or backward the number of updated records via LAG and LEAD functions to find the corresponding record.

Declare @table as table (data char(1));
Insert Into @table values ('a'),('b'),('c');

Declare @deleted as table (data char(1));
Insert Into @deleted Select * from @table;

Update @table set data = left(convert(char(36), newid()),1);
Declare @rows int = @@rowcount;

Declare @inserted as table (data char(1));
Insert Into @inserted Select * from @table;

Select * from @deleted;
Select * from @inserted;

Declare @history as table (data char(1), operation varchar(40), reccount int, stmp timestamp);
Insert Into @history Select *, 'pre update', @rows, NULL from @deleted
Insert Into @history Select *, 'post update', @rows, NULL from @inserted

Select * from @history

Select pre.*, post.* From
(Select *, LEAD(stmp, reccount) over (order by stmp) as leadstmp from @history) as pre
Inner Join @history as post
On post.stmp = pre.leadstmp
Where pre.operation='pre update' AND post.operation='post update'

First result shows @table before the update.
Second result shows @table after update.

Third result shows the log entries written.

And the last result analyzes the log (in the real world system this will run separate at nights) shows the records before/after in one row.
eg if 'a','b','c' have been updated to '1','3','2' this result will show the pairs ('a','1'),('b','3'),('c','2') - and all the additional history data.

This works fine without any sequence but the timestamp 'sequence' and what's more important even without any primary key in the @table, which otherwise could be used to relate before/after record pairs by the unchanged primary key, but not in a case like this a table just has one field (corner case). Also I want to detect changes of primary keys, which of course can't be detected, if relying on the prerequisite of them to stay constant.

This may have another problem, if the triggers runs concurrent and the log entries of one update are not in sequence in the history table. I think I'll add another value in there to partition by.

Bye, Olaf.