Home / Questions / 239032
Accepted
Hackerman
Asked: 2020-02-20 04:38:10 +0800 CST 2020-02-20 04:38:10 +0800 CST

Creating a trigger for validation [duplicate]

  • 772

I am in the consecutive 1070039332 , as seen in the image. But I can't let it get past consecutive 1070039400 , I don't have much experience with triggers.

enter image description here

Code:

create trigger restrinccion on [dbo].[tblencfacturas] for update 
as 
declare @ consecutivo int 

select @consecutivo = count(tblencfacturas.consecutivo) 
       from tblencfacturas 

if (@consecutivo>=1070039400) begin 
    print 'No se puede realizar la Factura, restrinccion sistemas.' 
end
sql

1 Answers

  1. Best Answer

    The problem is that the printon error does not raise any exception, therefore the transaction can be executed without problems. What you should do is raise an error usingRAISEERROR

    For example:

    create trigger restrinccion on [dbo].[tblencfacturas] for update 
as 
declare @ consecutivo int 

select @consecutivo = MAX(INSERTED.consecutivo) 
       from INSERTED

if (@consecutivo>=1070039400) begin 
    RAISERROR ('No se puede realizar la Factura, restrinccion sistemas.',
                                             -- Message text.  
               16,                           -- Severity.  
               1                             -- State.  
               ) 
end

    Details:

    • Severity : Or severity level can be configured by the user using levels 0 to 18.
    • State : An integer from 0 to 255, usually used to indicate in some way where the error occurred

    These two parameters can be useful if errors are eventually captured to have finer control over them.

    Additional notes

    I modified your code, since I understand that what you are looking for is to know if you are trying to add a value of consecutivogreater than a limit, what you were doing, select @consecutivo = count(tblencfacturas.consecutivo) from tblencfacturaswas not going to work:

    • It only returns the number of rows with values ​​other thanconsecutivo
    • In addition, it only takes into account the current rows and what you need is to verify the rows that are trying to be inserted or updated.

    For the last point, there are in the triggers, pseudo tables INSERTED or DELETED, with the first one we obtain the rows with the values ​​that we are going to update. So we can do:

    select @consecutivo = MAX(INSERTED.consecutivo) 
       from INSERTED

    With what we obtain the largest value consecutivothat we want to update or insert, we must take into account that a trigger is executed at the batch level, so you can either update one row or multiple, the previous statement is consistent with any of these forms.

    As Lamak points out, in MSSQL there is no instance BEFORE UPDATEfor a trigger, therefore the update will always be performed, the error is an event after this. Therefore, whenever you want to handle a disabling trigger you should handle a transaction and much better a block TRY .. CATCH .., in which case the RAISEERRORentire transaction can be caught and thrown away. In your example, for the trigger to work as you expect it to, you would do something like this:

    BEGIN TRANSACTION
BEGIN TRY

    update tblencfacturas set consecutivo = 1070039400 + 1

END TRY
BEGIN CATCH

    ROLLBACK TRANSACTION        
    PRINT ERROR_MESSAGE() -- Capturamos el mensaje del trigger

END CATCH
    • 4