How to skip rows during insert

  • Hello,

    I need to disallow a set of values from being inserted into a table.

    I am trying to set up a trigger that allows insert and update as long as that set of values are not present

    In the insert/update statement.

    So the following is the logic for the trigger:

    create TRIGGER check_null

    ON

    FOR INSERT, UPDATE

    AS

    BEGIN

    /* Get the range of level for this job type from the jobs table. */

    DECLARE @name varchar(100),

    @email varchar(100)

    SELECT @name = [name],

    @email =

    FROM inserted i

    IF (@name <> 'forbidden name') and (@email <> NULL)

    BEGIN

    INSERT INTO [BuyRefi].[dbo].[mls Member]

    ([name],

    )

    SELECT

    [name],

    FROM inserted i

    END

    ELSE

    BEGIN

    SKIP --- This is where the problem is

    END

    END

    I get the following error when I try to execute it : Incorrect syntax near 'SKIP'.

    How can I skip a row insertion?

    Many thanks in advance.

  • Why don't you simply use CHECK constraints?

    Another way could be using INSTEAD OF triggers and insert manually only the rows that pass your test.

    -- Gianluca Sartori

  • thank you for your prompt reply.

    I did not try it because the table is being used by DTS packages I get errors whenever I use the alter table command. Plus I am a noob when it comes to sql server. I will google it further but is there a way to skip a row during insertion? I am trying to learn how to do that.

  • Try with INSTEAD OF triggers.

    When a table has this kind of trigger, data is not inserted by the statement directly, but you have to insert data from the INSERTED logic table to the real table. You could easilly do it only for the rows that pass your check.

    Look in BOL for INSTEAD OF triggers.

    -- Gianluca Sartori

  • Hi, do not forget a trigger can be sent many rows at once, so your select with variables will not work.

    Use this kind of approach to solve your problem, only the rows that fit will be inserted in the table you want.

    You could also use a check constraint on the fields you want to be not null.

    Here's some piece of code:

    CREATE TRIGGER check_null

    ON

    FOR INSERT,UPDATE

    AS

    BEGIN

    /* Get the range of level for this job type from the jobs table. */

    INSERT INTO [BuyRefi].[dbo].[mls Member]

    ([name],

    )

    SELECT [name],

    FROM inserted i

    WHERE name 'forbidden name'

    AND Email IS NOT NULL

    END

    Cheers,

    J-F

  • Thanks, I'll look into it.

  • J-F's solution will work if you want to insert into a different table and not into the table on which the trigger is created. I don't understand if you're falling into the first or second case, maybe I misinterpreted the word "SKIP".

    I hope you understand what I mean in spite of my poor English.

    Regards

    Gianluca

    -- Gianluca Sartori

  • While you can do this, there is some question as to whether you should do it. The issue is that ofr most transactional methods, charges are "All Or Nothing". Which means if any inserted or updated record is invalid then all changes are rejected. If you use check constraints, then this is how they will work.

    If you are really sure that you want to filter changes instead of rejecting the transaction, then either After Triggers (which is what you posted) or Instead Of Triggers will work. Because you can only have one set of Instead Of triggers, it is usually preferred to use After Triggers (so that triggers for other purposs can be added later, without having to modify yours).

    Finally, the trigger example that you posted is written as though it is handling only a single row change, however, on SQL server triggers are fired once for each modifying statement, and not once for each modified row.

    Here is how I would write these triggers:

    create TRIGGER check_null ON

    FOR INSERT AS

    /* Since INSERTs and UPDATEs have to be handled differently

    I have seprated them. */

    BEGIN

    -- Delete any inserted rows that are invalid:

    DELETE From

    Join inserted ON

    .PrimaryKey = inserted.PrimaryKey

    Where inserted.name = 'forbidden name'

    OR inserted.email IS NULL

    END

    create TRIGGER check_null ON

    FOR UPDATE AS

    /* Since INSERTs and UPDATEs have to be handled differently

    I have seprated them. */

    BEGIN

    -- Reset any updated rows that are invalid:

    UPDATE

    Set [name] = deleted.name

    , email = deleted.email

    -- might need to reset all of the other columsn too?

    From

    Join deleted ON

    .PrimaryKey = deleted.PrimaryKey

    Where deleted.name = 'forbidden name'

    OR deleted.email IS NULL

    END

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply