SQL Trigger throws error but still inserts

I am working on a trigger that is supposed to block an insert when @verkoperstatus is 0; this does function, but for some reason it also stops the insert when @verkoperstatus is 1. What could be the root cause behind this?

CREATE TRIGGER [dbo].[verkoper_check] ON [dbo].[Verkoper]
FOR INSERT,UPDATE
AS
BEGIN
DECLARE @verkoperstatus bit 
DECLARE @gebruikersnaam varchar(25)
SELECT @gebruikersnaam = gebruikersnaam FROM inserted 
SELECT @verkoperstatus = verkoper FROM Gebruiker WHERE gebruikersnaam = @gebruikersnaam
    IF @verkoperstatus = 0
        BEGIN
            RAISERROR('Geen verkoper!',18,1);
            ROLLBACK;
        END
    ELSE
        BEGIN
            COMMIT;
        END
END

It should insert when @verkoperstatus is 1, and raise an error when @verkopstatus is 0.

The table Gebruiker is references, which includes a ‘gebruikersnaam’ and a ‘verkoper’ column. The value of the ‘gebruikersnaam’ column is the identifying column which (in this specific case is ‘Lars’). Verkoper is a bit column, which indicated if one is a seller or not, so this has the value of a 0 or a 1.

The goal I am trying to achieve is to have an insert on the Verkoper tabel if a ‘gebruikersnaam’ has the ‘verkoper’ value of one. This means if there is a row in Gebruiker with the ‘gebruikersnaam’ of Lars and the verkoper has a value of 1. This will be an allowed insert into the Verkoper tabel.

As the Verkoper has the following columns: ‘gebruikersnaam’, ‘banknaam’, ‘rekeningnummer’, ‘controleoptienaam’ and ‘creditcardnummer’. When ‘gebruikersnaam’ corresponds with a ‘gebruikersnaam’ from the Gebruikers table AND has a value of 1 in the ‘verkoper’ column this record will be allowed to be inserted into the Verkoper table.

As of now there is a row in the Gebruikers column which includes the gebruikersnaam ‘Lars’ and a verkoper value of ‘1’. Meaning any SQL Insert with the gebruikersnaam of ‘Lars’ should be allowed into the Verkoper table.

This however does not function the way I believe it should.

These are the tables mentioned above:

CREATE TABLE Verkoper (
    gebruikersnaam varchar(25) NOT NULL,
    banknaam varchar(255) NULL,
    rekeningnummer varchar(32) NULL,
    controleoptienaam char(10) NOT NULL,
    creditcardnummer integer NULL,
    CONSTRAINT pk_Verkoper PRIMARY KEY (gebruikersnaam),
    CONSTRAINT fk_Verkoper_Gebruikersnaam FOREIGN KEY (gebruikersnaam) REFERENCES Gebruiker(gebruikersnaam),
    CONSTRAINT ck_rekening CHECK (rekeningnummer is NOT NULL OR creditcardnummer is NOT NULL),
    CONSTRAINT ck_controleoptie CHECK (controleoptienaam IN('Post', 'Creditcard'))
)

CREATE TABLE  Gebruiker(
    gebruikersnaam varchar(25) NOT NULL,
    voornaam varchar(25) NOT NULL,
    achternaam varchar(25) NOT NULL,
    adresregel_1 varchar(255) NULL,
    adresregel_2 varchar(255) NULL,
    postcode char(7) NULL,
    plaatsnaam varchar(255) NULL,
    land varchar(255) NULL,
    geboortedag char(10) NOT NULL,
    mailbox varchar(255) NOT NULL,
    wachtwoord varchar(255) NOT NULL,
    verkoper bit NOT NULL,

CONSTRAINT pk_gebruiker PRIMARY KEY (gebruikersnaam),
)

For the inserts I am using the following data:

INSERT INTO Gebruiker VALUES ('Lars', 'Lars', 'Last_name', null, null, null, null, null, '04/04/2019', 'lars@mymailbox.cloud', 'MyPassword', 1)

INSERT INTO Verkoper VALUES ('Lars', 'ING', 'NL32ABN32492809', 'Post', null)

Answer

This is untested, however, I suspect this is the logic you really need:

CREATE TRIGGER [dbo].[verkoper_check] ON [dbo].[Verkoper]
FOR INSERT,UPDATE
AS BEGIN

    IF EXISTS(SELECT 1
              FROM inserted i
                   JOIN Gebruiker G ON i.gebruikersnaam = G.gebruikersnaam
              WHERE G.verkoper = 0) BEGIN

        RAISERROR('Geen verkoper!',18,1);
        ROLLBACK;
    END;
END;

Advertisement

Answer