CODEWITHSUNDEEP
sqlif-statementtriggersinsertsql-server-2012
J. Skinner
J. Skinner

Reputation: 319

IF statement with SQL Server trigger

I am a beginner with SQL and I'm trying to create a trigger to fire with an IF statement. Below is the trigger:

CREATE TRIGGER [Vector].[trg_insert_id]
    ON [vector].[a69]
    FOR INSERT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @OID int
    SELECT @OID = OBJECTID FROM INSERTED

    DECLARE @siteID float
    SELECT @siteID = MAX([SiteID]) FROM [vector].[a69]
    SELECT @siteID

    IF @OID NOT IN(select OBJECTID from vector.a69)
    BEGIN
        UPDATE [vector].[a69]
              SET SiteID = @siteID + 0.00001
              WHERE OBJECTID IN (@OID)  
    END
END

What I'm trying to have the trigger do is, after an insert to the table, search to see if the inserted OBJECTID already exists in the table. If it does not, update SiteID field by 0.00001.

When I have the IF statement, the trigger does not work. If I remove the IF statement, the trigger works, but it increments the SiteID field for other records with the same OBJECTID.

Any suggestions on what I'm doing wrong?

Upvotes: 1

Views: 9943

Answers (2)

HLGEM
HLGEM

Reputation: 96552

Might as well learn to use triggers correctly. First, if you ever find yourself writing something like:

   DECLARE @OID int
   SELECT @OID = OBJECTID FROM INSERTED

then your trigger is broken . It must be able to handle multiple values in the inserted or deleted pseudo-tables:

Next this part doesn't make logical sense

IF @OID NOT IN(select OBJECTID from vector.a69)
BEGIN
    UPDATE [vector].[a69]
          SET SiteID = @siteID + 0.00001
          WHERE OBJECTID IN (@OID)  
END

IF @OID doesn't exist in table vector.a60 then you can't update it. I am partly guessing what you want to do but I think this is what you actually want in the trigger.

CREATE TRIGGER [Vector].[trg_insert_id]
    ON [vector].[a69]
    FOR INSERT
AS
BEGIN
    SET NOCOUNT ON;

        UPDATE [vector].[a69]
              SET SiteID = @siteID + 0.00001
              WHERE OBJECTID IN (SLECT OID FROM inserted)  

END

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

NOT IN can be dangerous to use. If the subquery returns even a single NULL value, then the results is never true. This may be the cause of your problem. One simple fix is:

IF @OID NOT IN (select OBJECTID from vector.a69 where OBJECTID is not null)
. . .

I think a better approach is to use NOT EXISTS:

IF NOT EXISTS (select 1 from vector.a69 where OBJECTID = @OID)

Upvotes: 1

Related Questions