Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2002
    Posts
    23

    Unanswered: Trigger, If inserted is null...

    OK, a few years ago I was actually good at SQL Triggers. I guess between Oracle triggers and no triggers at all, I've lost it somewhere.

    Basically, I'm just trying to figure out how to check, on insert or update, if inserted.Dist or inserted.Subj is null. If so, populate with a value.

    I figured it would be something like...
    --------------------------------------------------
    CREATE TRIGGER [DefaultFields]
    ON [DBO].[Profile]
    FOR INSERT
    AS
    if inserted.Dist is null
    begin
    UPDATE [DBO].[Profile]
    SET Dist = 1040
    FROM Profile p
    JOIN inserted i
    ON p.primarykey = i.primarykey
    WHERE i.Dist IS NULL
    end

    if inserted.Subj is null
    begin
    UPDATE [DBO].[Profile]
    SET Subj = 1042
    FROM Profile p
    JOIN inserted i
    ON p.primarykey = i.primarykey
    WHERE i.Subj IS NULL
    end
    ---------------------------------------------------
    But this doesn't seem to work too well.
    Any suggestions??

  2. #2
    Join Date
    Nov 2002
    Posts
    23
    OK, I fixed one part...

    if (SELECT DIST FROM inserted) is null
    and
    if (SELECT SUBJ FROM inserted) is null

    instead of if inserted.dist is null.

    Now, my issue is trying to get the trigger to hit both if statements. Right now it's populating Dist properly but ignoring Subj all together. Any ideas?

  3. #3
    Join Date
    Oct 2002
    Posts
    369

    Lightbulb

    Originally posted by criki
    OK, I fixed one part... Now, my issue is trying to get the trigger to hit both if statements. Right now it's populating Dist properly but ignoring Subj all together.
    Q1 Any ideas?
    A1 How about using a case statement (such that both are checked simultaneously) and updated as appropriate. Yet another possibility would be to use a second trigger.

  4. #4
    Join Date
    Nov 2002
    Posts
    23
    I can't really use a case statement because it's 2 different fields that I'm looking at.

  5. #5
    Join Date
    Oct 2002
    Posts
    369

    Question

    Originally posted by criki
    Q1 I can't really use a case statement because it's 2 different fields that I'm looking at.
    A1 It is possible to update multiple columns in the same update statement. The logic I was thinking was essentially:

    use either a If -or- Case design to evaluate and choose the applicable update statement prior to the update?

    Case
    A is Null AND B is Null
    Do this kind of update (both)

    Case
    A is Null AND B is Not Null
    Do this other kind of update (A)

    Case
    A is Not Null AND B is Null
    Do this other different kind of update (B)

    Case
    A is Not Null AND B is Not Null
    Do something else

  6. #6
    Join Date
    Nov 2002
    Posts
    23
    Thank you!

    I was originally thinking that when I first started, but thought I could streamline it by taking out one statement.

  7. #7
    Join Date
    Oct 2002
    Posts
    369

    Smile

    RE:

    Thank you! I was originally thinking that when I first started, but thought I could streamline it by taking out one statement.
    You are welcome. It sounds like you were on the right track but got sidetracked somehow; it happens.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •