SQL Server 2016 – Trigger Issue

sql servertrigger

I need a little help with a trigger.
Where I'm in wrong:

CREATE TRIGGER [dbo].[tr_ins_MyTriggerName]
ON [dbo].MyTableName
FOR INSERT
AS 
    DECLARE 
    @Number nvarchar(20) ,
    @Date datetime ,
    @Supplier nvarchar(12);

    SET @Number = (SELECT Number FROM INSERTED);
    SET @Date = (SELECT Date FROM INSERTED);
    SET @Supplier = (SELECT Supplier FROM INSERTED);
BEGIN
    IF EXISTS (
        SELECT TOP 1 1 FROM MyTableName x
        WHERE x.Number = @Number
            AND x.Date = @Date
            AND x.Supplier = @Supplier
        )
    BEGIN
        RAISERROR ('There is the same Number for the same Supplier!' ,11,1)
        ROLLBACK
    END
END

This trigger runs in all cases even the the EXITS is not true!

Best Answer

I'd suggest you to create a unique index on the 3 columns, i.e. date / number / supplier.

If this is not applicable, I'd say you modify your trigger as follows

CREATE TRIGGER [dbo].[tr_ins_MyTriggerName]
ON [dbo].MyTableName
FOR INSERT
AS 
    DECLARE 
    @Number nvarchar(20) ,
    @Date datetime ,
    @Supplier nvarchar(12);

    SET @Number = (SELECT Number FROM INSERTED);
    SET @Date = (SELECT Date FROM INSERTED);
    SET @Supplier = (SELECT Supplier FROM INSERTED);
BEGIN
    IF  (
        SELECT count(*) FROM MyTableName x
        WHERE x.Number = @Number
            AND x.Date = @Date
            AND x.Supplier = @Supplier
        ) > 1
    BEGIN
        RAISERROR ('There is the same Number for the same Supplier!' ,11,1)
        ROLLBACK
    END
END

However, this trigger is problematic when you insert multiple values in one insert statement.

AFTER INSERT:

USE `TEST`;
DELIMITER $$
DROP TRIGGER IF EXISTS MEDICAMENTO.monthUpdateTriggerAI$$
USE `TEST`$$
    CREATE DEFINER = CURRENT_USER TRIGGER `TEST`.`monthUpdateTriggerAI` AFTER INSERT ON `TestTable` FOR EACH ROW
    BEGIN
    SET @COUNT=(SELECT COUNT(*) FROM TestTable2 WHERE (ItemId=NEW.ItemId AND Year=YEAR(NEW.Date) AND Month=MONTH(NEW.Date)));
    IF @COUNT=0 THEN
        INSERT INTO TestTable2 (ItemId,Year,Month,Open,Close,Increase ) VALUES 
        (NEW.ItemId , YEAR(NEW.Date), MONTH(NEW.Date),NEW.Value,NEW.Close,0.0); 
    ELSE
        UPDATE TestTable2 SET TestTable2.Close=NEW.Close AND Increase=(NEW.Close/(SELECT 
        Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date)) LIMIT 1))
        WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date));
    END IF;
    END;
$$
DELIMITER ;

AFTER UPDATE:

USE `TEST`;
DELIMITER $$
DROP TRIGGER IF EXISTS MEDICAMENTO.monthUpdateTriggerAU$$
USE `TEST`$$
    CREATE DEFINER = CURRENT_USER TRIGGER `TEST`.`monthUpdateTriggerAU` AFTER UPDATE ON `TestTable` FOR EACH ROW
BEGIN

    UPDATE TestTable2 SET TestTable2.Close=NEW.Close AND Increase=(NEW.Close/(SELECT 
    Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date)) LIMIT 1))
    WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date));

END;
$$
DELIMITER ;

Note: Be careful about the SELECT Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date)), I've added a LIMIT 1 to avoid duplicate rows.

Change the database TEST.

You can also use ON DUPLICATE KEY on the two triggers execution.

You can get more information in the MySQL's documentation manual.

Best Answer

Related Solutions

Sql-server – Oracle GoldenGate add trandata errors

MySQL Trigger – How to Create Trigger for Update or Insert in Another Table

AFTER INSERT:

AFTER UPDATE:

Related Question