June 20, 2012 at 8:54 pm
Unfortunately, triggers won't work in this case; SQL Server doesn't have a "commit trigger" that would wait until a COMMIT to check the table for valid structure. Consider the following valid sequence:
BEGIN TRANSACTION ;
DELETE mytable
WHERE ID = (SELECT MIN( ID) FROM mytable) ;
/* A delete trigger fails the above statement because there ae only 4 rows in the table */
DECLARE @I INT ;
SELECT @I = MAX( ID) FROM mytable ;
SET @I = @I + 1 ;
INSERT mytable (ID, Date, Description)
VALUES ( @I, GETDATE(), "Notes " + CAST(@I AS VARCHAR(10)) );
/* Now we would have 5 rows!!! */
COMMIT TRANSACTION ;
David Lathrop
DBA
WA Dept of Health
June 20, 2012 at 9:06 pm
DLathrop (6/20/2012)
Unfortunately, triggers won't work in this case; SQL Server doesn't have a "commit trigger" that would wait until a COMMIT to check the table for valid structure. Consider the following valid sequence:BEGIN TRANSACTION ;
DELETE mytable
WHERE ID = (SELECT MIN( ID) FROM mytable) ;
/* A delete trigger fails the above statement because there ae only 4 rows in the table */
DECLARE @I INT ;
SELECT @I = MAX( ID) FROM mytable ;
SET @I = @I + 1 ;
INSERT mytable (ID, Date, Description)
VALUES ( @I, GETDATE(), "Notes " + CAST(@I AS VARCHAR(10)) );
/* Now we would have 5 rows!!! */
COMMIT TRANSACTION ;
Like I said - complex requirements. 🙂 In this case they require a certain coding pattern. Once you have 5 rows in the table and those 5 rows meet all requirements you put the 3 triggers is in place. From that moment forward it would seem that you can only UPDATE an existing row with a valid update statement. The DELETE and INSERT triggers will prevent unwanted actions of those types which 'could' leave the table in an invalid state. Thinking about it this makes the triggers easier since DELETE and INSERT simply raise an error and rollback the tran. :hehe:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 21, 2012 at 4:31 am
who'd have thought something so simple could have created as much debate :-), and thanks for all the input so far. As a bit more explanation:
This relates to a logging table which records jobs running. When I said constantly changing data what I meant was that I will already have created a ## table which each day will look for the existance of 5 sucessful jobs (because I know what their wording will be, which I will write to ##.Description) linked to a certain process, and also note their ID (internal ID already assigned) and timestamp. I know what the five jobs are in terms of their description but they either don't all run, or when they do there can be a variation in their standard running order. I just need to a) count that there are 5 jobs in total each day (straightforward) and then b) check the running order (based on 'Description') matches their ascending ID/timestamp. Its the syntax of this second part I am struggling with.
June 22, 2012 at 8:55 am
This might work, but the values need to be unique in each of the 3 columns. The SQL script creates and joins three tables in a CTE. Table one has row numbers sorted by ID, table two has row numbers sorted by date and table three has row numbers sorted by description. Five rows are returned in the CTE only if your ordering conditions are met.
declare @ztbl table (id int, rdate datetime, Description varchar(25))
insert into @ztbl values (221, '1/11/12 09:01', 'Note1')
insert into @ztbl values (222, '1/11/12 09:02', 'Note2')
insert into @ztbl values (225, '1/11/12 09:08', 'Note3')
insert into @ztbl values (678, '1/11/12 10:00', 'Note4')
insert into @ztbl values (898, '1/11/12 10:33', 'Note5')
;
--------------------------
WITH OrderedFive_CTE
AS
(
select * from (
select row_number() OVER (ORDER BY id) AS id_sorted,
rdate as d1,id as id1, Description as n1
from @ztbl ) z1
INNER JOIN
(select row_number() OVER (ORDER BY rdate) AS date_sorted,
rdate as d2,id as id2, Description as n2
from @ztbl ) z2
on z1.id1 = z2.id2
INNER JOIN
(select row_number() OVER (ORDER BY Description) AS desc_sorted,
rdate as d3,id as id3, Description as n3
from @ztbl ) z3
on z2.id2 = z3.id3
)
--query the CTE
select *
from OrderedFive_CTE
where id_sorted=date_sorted and date_sorted = desc_sorted
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply