CODEWITHSUNDEEP
sql-servertriggers
hollyx
hollyx

Reputation: 99

Error in SQL Server trigger

I am writing a trigger for the first time. I want to check that if value =4 then Exon N, Date Fin,Date début must not be null

CREATE TRIGGER tgr_suspTVA
   ON dbo.F_COMPTET
   AFTER INSERT
AS 
BEGIN
    DECLARE @N_CatCompta int, @ExonN varchar(69),
            @dateFin datetime, @dateDeb datetime 

    SELECT @N_CatCompta = N_CatCompta, 
           @ExonN = [Exon N°], 
           @dateFin = [Date Fin],
           @dateDeb = [Date début]

    IF (@N_CatCompta=4) AND (@ExonN IS NULL OR  @dateFin IS NULL OR @dateDeb IS NULL)
    BEGIN
          RAISERROR('error',16,1);
    END
END;

Here is the error that I get:

Msg 207, Niveau 16, État 1, Procédure tgr_suspTVA, Ligne 13
Nom de colonne non valide : 'N_CatCompta'.

Msg 207, Niveau 16, État 1, Procédure tgr_suspTVA, Ligne 13
Nom de colonne non valide : 'Exon N°'.

Msg 207, Niveau 16, État 1, Procédure tgr_suspTVA, Ligne 13
Nom de colonne non valide : 'Date Fin'.

Msg 207, Niveau 16, État 1, Procédure tgr_suspTVA, Ligne 14
Nom de colonne non valide : 'Date début'.

Upvotes: 1

Views: 143

Answers (2)

Sean Lange
Sean Lange

Reputation: 33581

Although I think the check constraint that Tab Alleman posted is a better approach you might be stuck using a trigger.

If that is the case, you have two MAJOR problems here. The select statement for populating your variables doesn't have a FROM clause. The bigger issue is that you are using scalar variables in your trigger. Triggers fire once per operation in sql server not once per row. You need to reference the inserted virtual table and handle your code appropriately. Most likely an exists.

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31785

Triggers are not the best way to constrain your data and raise errors when an insert tries to break your rules.

The best way to do this is with constraints. Even a complex rule like this can be handled with a simple CHECK constraint:

ALTER TABLE dbo.F_COMPTET
ADD CONSTRAINT chkCompta4 CHECK (N_CatCompta<>4 OR 
([Exon N°] IS NOT NULL  
 AND [Date Fin] IS NOT NULL
 AND [Date début] IS NOT NULL
));

Add this constraint to your table, and any insert that tries to break this rule will raise a constraint violation error.

A trigger would be useful if, when a row breaks your rule, you wanted to do an insert into a different table. But as long as you are only dealing with the one table, there is no need for a trigger.

Upvotes: 1

Related Questions