Insert Trigger

[BigMickeyD]

I'm new to writing triggers and could use some help.

The table contains makeup requests, which have a requestID,
a status (R - requested, P-Processed) and a starting and
ending document number.

MR_RequestID MR_Status MR_StartDoc MR_EndDoc
12345 P 0000012345 0000024792
12346 R 0000127342 0000127353
12347 R 0000162837 0001234567
...
23456 R 0000162837 0000213670

The insert trigger should disallow adding a new record where the starting and ending numbers being inserted exist in any of the existing requests at Status 'R'. Otherwise it's OK to insert the record.

We thought about exploding the numbers and having a row for each document but that would produce a large table on the order of 10 million rows.

Any ideas or suggestions would be appreciated. Is a trigger the best approach?

Thanks

 

[sahernandez]

Why don't you made a Insert Batch, and this insert you can block all the rows that you dont' want to insert, doesn't matter if you gonna insert 1 row or 10 millions.

you can also put this into trigger like this

insert into the_same_Table (MR_RequestID,MR_Status,MR_StartDoc,MR_EndDoc)
select a.MR_RequestID,a.MR_Status,a.MR_StartDoc,a.MR_EndDoc
from inserted a --- CHECK this is that Diff
left join the_same_Table b
on a.MR_RequestID = b.MR_RequestID and a.MR_Status and b.MR_Status and a.MR_RequestID is null

 

[osjohnm]

Hi

If I am understanding u correctly this mustn't happen:

MR_RequestID MR_Status MR_StartDoc MR_EndDoc
23456 R 0000162837 0000213670
25469 R 0000162837 0000213670

or
12345 P 0000012345 0000024792
12388 P 0000012345 0000024792

So if a record can only appear once with status 'R' startdoc, enddoc and once with status 'P' and then the same startdoc, enddoc as the 'R' record then why not create a UNIQUE CONSTRAINT across the MR_status, MR_StartDoc, MR_EndDoc columns instead of a trigger.

If you attempt to insert a record that already exists then it will fail do to the UNIQUE constraint.

But if a the docs can appear multiple times with status 'P'
then the unique constraint wouldn't work.

Just a thought

John

 

[BigMickeyD]

I've made some progress I think, but I'm still having
problems.

MR_RequestID MR_Status MR_StartDoc MR_EndDoc
12345 R 0000012345 0000024792
12346 R 0000127342 0000127353
12347 R 0000162837 0001234567
...
23456 R 0000162837 0000213670

The concern is with the range of numbers being inserted, not the primary key which is just an identity. The fact I used the same number may have been misleading.

There's a number of cases,
(For all cases we only care about Status 'R')

Case 1 range 0000012355 0000024782
Case 2 range 0000012335 0000024770
Case 3 range 0000012349 0000024795
Case 4 range 0000012343 0000024798

1 The new range is completely included in an existing request range
2 The new range overlaps at the beginning of an existing request range
3 The new range overlaps at the end of an existing request range
4 The new range contains an entire existing request range


If any of these cases occur the insert should be disallowed

I've created this trigger:

CREATE TRIGGER [MakeUpRequests_InsTrg] ON MakeUpRequests
FOR INSERT
AS
/* */
Set NoCount On
/*
Declare local vars.
*/
Declare @InsStart int,
@InsEnd int
/* */
select @InsStart = MR_StartDoc , @InsEnd = MR_EndDoc from inserted
/* */
If Exists (
SELECT ID FROM MakeUpRequests
WHERE (Status = 'R') AND
((@InsStart BETWEEN MR_StartDoc AND
MR_EndDoc )
OR
(@InsEnd BETWEEN MR_StartDoc AND
MR_EndDoc ))
)
Begin
Rollback Transaction
Raiserror (50001, 16, 1, 'Case 1 2 or 3'
, @insStart, @InsEnd)
Return
End
Else
If Exists (
SELECT ID FROM MakeUpRequests
WHERE (Status = 'R') AND
(( MR_StartDoc BETWEEN @InsStart
AND @InsEnd) AND
(MR_EndDoc BETWEEN @InsStart AND @InsEnd))
)
Begin
Rollback Transaction
Raiserror (50001, 16, 1, 'Case 4',
@insStart, @InsEnd)
Return
End
/* --------- */

Now the problem I'm having is the insert is always
disallowed. The script is always firing the first
If Exists.

Suggestions?