Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104

    Question Unanswered: What's wrong with my TRIGGER?

    I am creating a JOBNO using a trigger (VB front end). The code below works fine. However, when I insert a new record (via SPROCS) or manually and the JOBTYPE is not 'TAL', it always replaces the assigned value for the JOBNO to null.

    I removed the trigger and my SPROCS and manual entry works fine. Any ideas would be appreciated.

    Thank you.



    Code:
    CREATE TRIGGER tr_CREATE_TATL_JOBNO ON [WorkOrder] 
    FOR INSERT
    AS
    
    Declare @JOBNO varchar(10)
    Declare @JobType varchar(3)
    Declare @WorkOrderID int
    Declare @VRUTimeLine varchar(7)
    
    SELECT @JobType = JobType FROM inserted
    SELECT @WorkOrderID=WorkOrderID FROM inserted
    SELECT @VRUTimeLine = VRUTimeLine FROM inserted
    
    IF @JobType="TAL" 
    	IF LEN(LTRIM(STR(@WorkOrderID))) < 6
    		BEGIN
    			SET @JOBNO = LEFT(@VRUTimeLine,2) + RIGHT(@VRUTimeLine,2) +  REPLICATE('0',6-LEN(LTRIM(STR(@WorkOrderID)))) + LTRIM(STR(@WorkOrderID))
    		END
    	ELSE
    		BEGIN
    			SET @JOBNO =  LEFT(@VRUTimeLine,2) + RIGHT(@VRUTimeLine,2) + RIGHT(STR(@WorkOrderID),6)
    		END		
    	
    UPDATE WorkOrder SET JobID=@JOBNO WHERE WorkOrderID=@WorkOrderID

  2. #2
    Join Date
    Mar 2003
    Location
    Indiana, USA
    Posts
    100
    You have to write triggers to work for multiple/single and no rows being updated. You code is only for an single row update.

    Tim S

    My guess on what you want follows:

    I only rarely do triggers ( and then normally update triggers) so I might have many mistakes in it.

    Code:
    CREATE TRIGGER tr_CREATE_TATL_JOBNO ON [WorkOrder] 
    FOR INSERT
    AS
    
    UPDATE WO 
    SET    JobID = CASE WHEN LEN(LTRIM(STR(i.WorkOrderID))) < 6
                       THEN LEFT(i.VRUTimeLine,2) + RIGHT(i.VRUTimeLine,2) +  REPLICATE('0',6-LEN(LTRIM(STR(i.WorkOrderID)))) + LTRIM(STR(i.WorkOrderID))
                   ELSE LEFT(i.VRUTimeLine,2) + RIGHT(i.VRUTimeLine,2) + RIGHT(STR(i.WorkOrderID),6)
    FROM  WorkOrder WO 
    JOIN  inserted i ON i.PK = WO.PK -- replace PK with the Primary key of WorkOrder
    WHERE i.JobType = 'TAL'   
    
    -- Here my second guess; I think insert triggers might be like below.
    
    CREATE TRIGGER tr_CREATE_TATL_JOBNO ON [WorkOrder] 
    FOR INSERT
    AS
    
    UPDATE i
    SET    i.JobID = CASE WHEN LEN(LTRIM(STR(i.WorkOrderID))) < 6
                       THEN LEFT(i.VRUTimeLine,2) + RIGHT(i.VRUTimeLine,2) +  REPLICATE('0',6-LEN(LTRIM(STR(i.WorkOrderID)))) + LTRIM(STR(i.WorkOrderID))
                   ELSE LEFT(i.VRUTimeLine,2) + RIGHT(i.VRUTimeLine,2) + RIGHT(STR(i.WorkOrderID),6)
    FROM   inserted i 
    WHERE i.JobType = 'TAL'
    Last edited by TimS; 06-04-03 at 23:29.

  3. #3
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104

    Thumbs up

    Thank you for your reply.

    My trigger actually works however, to resolve my problem, it was pointed out to me that I needed to encapsulate the whole sql statement after the IF with BEGIN...END. With out this, the "UPDATE Workorder...."
    gets executed no matter what.

    I also see how you handled it in such a way that I won't need the IF, BEGIN..END statements.

    Can you share some code samples of triggers for multiple rows? It was also pointed out to me that I may have a potential problem when bulk inserting since my trigger was designed to handle only one record insert at a time.

    Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •