Incrementing a Number in a Field Through a SQL Job

For our accounting software system, it's not a good idea to manipulate it by adding triggers that are not a part of the proprietary programming.

That's right.  And it's equally bad to manipulate data within a proprietary system with rogue programs or procedures.  After all, a trigger is just a special kind of procedure that runs automatically.

But then again, I'm guessing you've already dismissed the preferred alternative which is to work with your vendor to have your requirements implemented within the proprietary product.  I know there are lots of excuses:  you need the fix now, not later; they charge too much for customizations; the product works as designed, etc, etc, etc.

The reality is with a vendor package you sometimes get caught between a rock and a  hard spot; you cannot both follow best practices and meet your needs.  So, what to do?

Whatever you decide, it should be treated as a "managed" change.  By that I mean, somebody needs to keep very good track of it and reinstall/test the code every time the product is reinstalled/upgraded.

Before rejecting the idea of a trigger outright,  look at objectively at the pros/cons between doing that way compared to an SQL job.  

Let's see...the trigger runs instantly and automatically.  The SQL job must be scheduled.  How often?  Every time a new document number is assigned? Every time somebody notices the next number seed has been corrupted?  Once an hour?

Why not just train everybody to override the prefix back to UNIVTR when it goes bad?  





)
 




































 

 

Here's some  (untested) for a procedure


Update IV40100
Set NXTSPNUM='UNIVTR' + right(replicate('0',8) + isnull(cast(
(Select Max(Cast(DOCNUMBER as INT)) FROM
(Select RIGHT(DOCNUMBER,9) From SVC00700
where DOCNUMBER LIKE 'UNIVTR%'
and isnumeric(RIGHT(DOCNUMBER,9))
union Select RIGHT(DOCNUMER,9) from SVC30700
where DOCNUMBER LIKE 'UNIVTR%'
and isnumeric(RIGHT(DOCNUMBER,9))
)) as VARCHAR(9) ), 1)


Here's some code (untested) for a trigger:
CREATE TRIGGER PreservePrefix
ON IV40100
INSTEAD OF UPDATE
BEGIN
Update IV40100
Set NXTSPNUM=ISNULL(
(Select Max(NXTSPNUM) FROM Inserted where LEFT(NXTSPNUM,6) = 'UNIVTR')
,NXTSPNUM)
END

Those are some very good points, dqmq!  Unfortunately, the vendor is not willing to fix.  And, I hate to ask the client to have to monitor a field that should not require monitoring.  That's why I was hoping to just find a T-SQL script that says "update IV40100 set NXTSPNUM = "one higher than the highest UNIVTR number whether that number is in the SVC00700 table or in the SVC30700 table"".

Is there anyway that you can help me compose such a script?  I'm confident that I can figure out the best way to use it, from there.  I jst need help with the syntax.

Thanks, dqmq!!!  I'll let you know, if I have anything further on this.

Hi dqmq:
 
I ran the code, but I got two error messages as follows:

Msg 4145, Level 15, State 1, Line 7
An expression of non-boolean type specified in a context where a condition is expected, near 'union'.
Msg 4145, Level 15, State 1, Line 10
An expression of non-boolean type specified in a context where a condition is expected, near ')'.

The only differences between the code that I used and what you sent is that I changed the "DOCNUMBER" field to the correct name of "ORDDOCID", and I changed 9 to 8 since the number of characters after "UNIVTR" is 8.  

Below is the code.  By the way, what does the clause "replicate('0',8)" do?

Update IV40100
Set NXTSPNUM='UNIVTR'+ right(replicate('0',8) + isnull(cast(
(Select Max(Cast(ORDDOCID as INT)) FROM
(Select RIGHT(ORDDOCID, 8) From SVC00700
where ORDDOCID LIKE 'UNIVTR%'
and isnumeric(RIGHT(ORDDOCID, 8))
union Select RIGHT(ORDDOCID, 8) from SVC30700
where ORDDOCID LIKE 'UNIVTR%'
and isnumeric(RIGHT(ORDDOCID, 8))
)) as VARCHAR(8) ), 1)

Like I said, it was untested.  I see no issues with the changes you made, so let's first see if this runs:

                           Select RIGHT(ORDDOCID, 8) as ORDDOCID From SVC00700
                           where ORDDOCID LIKE 'UNIVTR%'
                           and isnumeric(RIGHT(ORDDOCID, 8))
                           union Select RIGHT(ORDDOCID, 8) from SVC30700
                           where ORDDOCID LIKE 'UNIVTR%'
                           and isnumeric(RIGHT(ORDDOCID, 8))


Then, see if this runs:
                 Select
                          Max(Cast(ORDDOCID as INT)) FROM
                          (
                           Select RIGHT(ORDDOCID, 8) as ORDDOCID From SVC00700
                           where ORDDOCID LIKE 'UNIVTR%'
                           and isnumeric(RIGHT(ORDDOCID, 8))
                           union Select RIGHT(ORDDOCID, 8) from SVC30700
                           where ORDDOCID LIKE 'UNIVTR%'
                           and isnumeric(RIGHT(ORDDOCID, 8))
                           ) as tmp


Then this:
        Select
                 ISNULL
                     (  
                          Max(Cast(ORDDOCID as INT)) FROM
                          (
                           Select RIGHT(ORDDOCID, 8) as ORDDOCID From SVC00700
                           where ORDDOCID LIKE 'UNIVTR%'
                           and isnumeric(RIGHT(ORDDOCID, 8))
                           union Select RIGHT(ORDDOCID, 8) from SVC30700
                           where ORDDOCID LIKE 'UNIVTR%'
                           and isnumeric(RIGHT(ORDDOCID, 8))
                           ) as tmp
                     ,1)
               
Then this:

        Select
              CAST
              (
                 ISNULL
                     (  
                          Max(Cast(ORDDOCID as INT)) FROM
                          (
                           Select RIGHT(ORDDOCID, 8) as ORDDOCID From SVC00700
                           where ORDDOCID LIKE 'UNIVTR%'
                           and isnumeric(RIGHT(ORDDOCID, 8))
                           union Select RIGHT(ORDDOCID, 8) from SVC30700
                           where ORDDOCID LIKE 'UNIVTR%'
                           and isnumeric(RIGHT(ORDDOCID, 8))
                           ) as tmp
                      ,1
                      )
                as VARCHAR(8)
                )
               

Hi dqmq:

On the first script, I get the following errors:
Msg 4145, Level 15, State 1, Line 4
An expression of non-boolean type specified in a context where a condition is expected, near 'union'.
Msg 4145, Level 15, State 1, Line 6
An expression of non-boolean type specified in a context where a condition is expected, near ')'.
 
On the second script, I get the following errors:
Msg 4145, Level 15, State 1, Line 7
An expression of non-boolean type specified in a context where a condition is expected, near 'union'.
Msg 4145, Level 15, State 1, Line 10
An expression of non-boolean type specified in a context where a condition is expected, near ')'.

On the third script, I get the following errors:
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'FROM'.
Msg 4145, Level 15, State 1, Line 9
An expression of non-boolean type specified in a context where a condition is expected, near 'union'.
Msg 4145, Level 15, State 1, Line 12
An expression of non-boolean type specified in a context where a condition is expected, near ')'.

On the fourth script, I get the following errors:
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'FROM'.
Msg 4145, Level 15, State 1, Line 11
An expression of non-boolean type specified in a context where a condition is expected, near 'union'.
Msg 4145, Level 15, State 1, Line 14
An expression of non-boolean type specified in a context where a condition is expected, near ')'.

What database are  you using?

"TUNIV" is the database ID.

No, I meant what dbms are you using:   Access, DB2, SQL Server, mySQL?

SQL Server 2005 Standard.

OK, try this:

Update IV40100
Set NXTSPNUM=(SELECT 'UNIVTR' +  right(replicate('0',7),8) + CAST (ORDDOCID AS VARCHAR(8)) FROM
                          (
                          SELECT
                           ISNULL(Max(Cast(ORDDOCID as INT)),1) AS ORDDOCid FROM
                          (
                           Select RIGHT(ORDDOCID, 8) as ORDDOCID From SVC00700
                           where ORDDOCID LIKE 'UNIVTR%'
                           and isnumeric(RIGHT(ORDDOCID, 8)) = 1
                           union Select RIGHT(ORDDOCID, 8) from SVC30700
                           where ORDDOCID LIKE 'UNIVTR%'
                           and isnumeric(RIGHT(ORDDOCID, 8)) = 1
                           ) as tmp
                        ) as tmp)