reference from one table to another

  • Jeff Maahs

    Old Hand

    Points: 374

    Having trouble getting it to work.

    CREATE TRIGGER photo_numbers

    ON mls_photo

    FOR INSERT , UPDATE

    as

    declare @PhotoState varchar(20)

    set @PhotoState = case when inserted.medium_photo is null then NULL else '1' end

    + case when inserted.medium_photo2 is null then NULL else '2' end

    + case when inserted.medium_photo3 is null then NULL else '3' end

    + case when inserted.medium_photo4 is null then NULL else '4' end

    + case when inserted.medium_photo5 is null then NULL else '5' end

    + case when inserted.medium_photo6 is null then NULL else '6' end

    + case when inserted.medium_photo7 is null then NULL else '7' end

    + case when inserted.medium_photo8 is null then NULL else '8' end

    + case when inserted.medium_photo9 is null then NULL else '9' end

    insert into mls(p_mod_date_time, photo)

    values (Left(CONVERT( varchar , getdate() , 1),8), @PhotoState)

    It complains that "The column prefix 'inserted' does not match with a table name or alias name used in the query."

    If I take out the "inserted." then it states that "Invalid column name 'medium_photo'." and the same for all columns...hmmm

  • rb

    SSC Enthusiast

    Points: 129

    Hi,

    I am quite late in the discussion.

    But the problem with your query is that it needs a select rather than a Set.

    ___________________________________

    CREATE TRIGGER photo_numbers

    ON mls_photo

    FOR INSERT , UPDATE

    as

    declare @PhotoState varchar(20)

    Select @PhotoState = case when inserted.medium_photo is null then NULL else '1' end

    + case when inserted.medium_photo2 is null then NULL else '2' end

    + case when inserted.medium_photo3 is null then NULL else '3' end

    + case when inserted.medium_photo4 is null then NULL else '4' end

    + case when inserted.medium_photo5 is null then NULL else '5' end

    + case when inserted.medium_photo6 is null then NULL else '6' end

    + case when inserted.medium_photo7 is null then NULL else '7' end

    + case when inserted.medium_photo8 is null then NULL else '8' end

    + case when inserted.medium_photo9 is null then NULL else '9' end

    from inserted

    insert into mls(p_mod_date_time, photo)

    values (Left(CONVERT( varchar , getdate() , 1),8), @PhotoState)

    _____________________

    Post Again in case of problems.

    Regards,

    RB

  • Jeff Maahs

    Old Hand

    Points: 374

    Thanks!

    I had someone send me this which actually adds to the database. However, when I update medium_photo2 = NULL, I get:

    Server: Msg 208, Level 16, State 1, Procedure photo_numbers, Line 6

    Invalid object name 'mls'.

    CREATE TRIGGER photo_numbers

    ON mls_photo

    FOR INSERT , UPDATE

    as

    insert into mls(p_mod_date_time, photo)

    Select Left(CONVERT( varchar , getdate() , 1),8),

    case when inserted.medium_photo is null then '0' else '1' end

    + case when inserted.medium_photo2 is null then '0'else '2' end

    + case when inserted.medium_photo3 is null then '0'else '3' end

    + case when inserted.medium_photo4 is null then '0'else '4' end

    + case when inserted.medium_photo5 is null then '0'else '5' end

    + case when inserted.medium_photo6 is null then '0'else '6' end

    + case when inserted.medium_photo7 is null then '0'else '7' end

    + case when inserted.medium_photo8 is null then '0'else '8' end

    + case when inserted.medium_photo9 is null then '0'else '9' end

    From Inserted

    Any ideas?

  • Paul Ibison

    SSCertifiable

    Points: 5303

    My trigger should have been:

    set @PhotoState = (select case when inserted.large_photo is null then NULL else '1' end

    + case when inserted.medium_photo2 is null then NULL else '2' end

    + case when inserted.medium_photo3 is null then NULL else '3' end

    + case when inserted.medium_photo4 is null then NULL else '4' end

    + case when inserted.medium_photo5 is null then NULL else '5' end

    + case when inserted.medium_photo6 is null then NULL else '6' end

    + case when inserted.medium_photo7 is null then NULL else '7' end

    + case when inserted.medium_photo8 is null then NULL else '8' end

    + case when inserted.medium_photo9 is null then NULL else '9' end from inserted)

    Even so this doesn't work: Didn't know this, but "Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.". However, you could still use the calculated field (case statement) on the mls_photo table and define a trigger on this table which transfers the contents of the calculated column to the mls_table - this also makes the trigger really simple.

    Paul Ibison

    Paul.Ibison@btinternet.com


    Paul Ibison
    Paul.Ibison@replicationanswers.com

  • Jeff Maahs

    Old Hand

    Points: 374

    I also got the error about the text fields but only when I tried to specify the mls table more by using vreb.dbo.mls(...

    CREATE TRIGGER photo_numbers

    ON mls_photo

    FOR INSERT , UPDATE

    as

    insert into mls(p_mod_date_time, photo)

    Select Left(CONVERT( varchar , getdate() , 1),8),

    case when inserted.medium_photo is null then '0' else '1' end

    + case when inserted.medium_photo2 is null then '0' else '2' end

    + case when inserted.medium_photo3 is null then '0' else '3' end

    + case when inserted.medium_photo4 is null then '0' else '4' end

    + case when inserted.medium_photo5 is null then '0' else '5' end

    + case when inserted.medium_photo6 is null then '0' else '6' end

    + case when inserted.medium_photo7 is null then '0' else '7' end

    + case when inserted.medium_photo8 is null then '0' else '8' end

    + case when inserted.medium_photo9 is null then '0' else '9' end

    From Inserted

    This trigger above actually adds to the database ok, its just when it tries to run the update, it doesn't recognize the mls table.

    If I can do it this way, then I can solve all my problems that are occuring and the programmers don't have to make as many changes and this could actually be implemented.

    So if anyone can help Paul and I out - it would be great!

  • nigelrivett

    SSCertifiable

    Points: 5362

    try joining to the source table to access the image field (bit surprised you don't want the id in mls).

    This shouldn't affect you getting an error on the mls table but you never know.

    CREATE TRIGGER photo_numbers

    ON mls_photo

    FOR INSERT , UPDATE

    as

    insert into mls(p_mod_date_time, photo)

    Select Left(CONVERT( varchar , getdate() , 1),8),

    case when inserted.medium_photo is null then '0' else '1' end

    + case when m.medium_photo2 is null then '0' else '2' end

    + case when m.medium_photo3 is null then '0' else '3' end

    + case when m.medium_photo4 is null then '0' else '4' end

    + case when m.medium_photo5 is null then '0' else '5' end

    + case when m.medium_photo6 is null then '0' else '6' end

    + case when m.medium_photo7 is null then '0' else '7' end

    + case when m.medium_photo8 is null then '0' else '8' end

    + case when m.medium_photo9 is null then '0' else '9' end

    From Inserted, mls_photo m

    where Inserted.id = m.id


    Cursors never.
    DTS - only when needed and never to control.

  • Paul Ibison

    SSCertifiable

    Points: 5303

    Phred,

    strange - even with your revised syntax I still get the message "Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.".

    So, I'd decide to use the alternative method which works fine:

    have a computed column on mls_photo which we have worked out in this thread, and a simple trigger on mls_photo which inserts/updates etc the corresponding record on mls.

    Paul Ibison

    Paul.Ibison@btinternet.com


    Paul Ibison
    Paul.Ibison@replicationanswers.com

  • Jeff Maahs

    Old Hand

    Points: 374

    Hi Paul,

    Yes, that seems like the way to go. I have the computed field but I am having trouble with the trigger. This is my first experience with them and the book I have barely touches them.

    If I say please? Please!

  • nigelrivett

    SSCertifiable

    Points: 5362

    I'd left one reference to inserted which is probably why it fails

    CREATE TRIGGER photo_numbers

    ON mls_photo

    FOR INSERT , UPDATE

    as

    insert into mls(p_mod_date_time, photo)

    Select Left(CONVERT( varchar , getdate() , 1),8),

    case when m.medium_photo is null then '0' else '1' end

    + case when m.medium_photo2 is null then '0' else '2' end

    + case when m.medium_photo3 is null then '0' else '3' end

    + case when m.medium_photo4 is null then '0' else '4' end

    + case when m.medium_photo5 is null then '0' else '5' end

    + case when m.medium_photo6 is null then '0' else '6' end

    + case when m.medium_photo7 is null then '0' else '7' end

    + case when m.medium_photo8 is null then '0' else '8' end

    + case when m.medium_photo9 is null then '0' else '9' end

    From Inserted, mls_photo m

    where Inserted.id = m.id


    Cursors never.
    DTS - only when needed and never to control.

  • Jeff Maahs

    Old Hand

    Points: 374

    Hi nigelrivett,

    I still get: Invalid object name 'mls'.

    If "Inserted" is the mls_photo record of the inserted/updated record, should the last line reference the mls.id?

    Posing the question because I am having trouble following this. I have cross table updates before but that was between 2 databases and with Inserted, I am looking at this like it is actually three. My lack of knowledge is showing, I know.

    Just so you know, the mls record has to be there before the mls_photo record is added or modified. So there is never a chance of a mls_photo.id without a corresponding mls.id.

    I do appreciate everyones help! Keep it coming please.

  • nigelrivett

    SSCertifiable

    Points: 5362

    You have a mls_photo table which has the trigger on it and you want to insert into the mls table in the trigger?

    If that's the case then

    insert mls (...)

    select ...

    from insertd, mls_photo

    ...

    should work.

    mls is only referenced in the insert - if you get an object not found it must be because the table isn't there. Either you have lost it or it isn't really called this.


    Cursors never.
    DTS - only when needed and never to control.

  • Jeff Maahs

    Old Hand

    Points: 374

    Ah,

    vreb.dbo.mls

    vrebphoto.dbo.mls_photo

    I suppose this makes a difference.

  • Jeff Maahs

    Old Hand

    Points: 374

    But one problem. "Cannot insert the value NULL into column 'list_date', table 'vreb.dbo.mls'; column does not allow nulls. INSERT fails.

    The statement has been terminated."

    This is a field in the mls table. This would mean it is trying to insert a totally new record in the mls table.

    So I should actually change the insert to an update since the mls record will always be there before the mls_photo record is created.

    So I did this:

    --drop trigger photo_numbers

    CREATE TRIGGER photo_numbers

    ON mls_photo

    FOR INSERT , UPDATE

    as

    update [vreb].[dbo].mls set p_mod_date_time=Left(CONVERT( varchar , getdate() , 1),8), photo=

    case when m.medium_photo is null then '' else '1' end

    + case when m.medium_photo2 is null then '' else '2' end

    + case when m.medium_photo3 is null then '' else '3' end

    + case when m.medium_photo4 is null then '' else '4' end

    + case when m.medium_photo5 is null then '' else '5' end

    + case when m.medium_photo6 is null then '' else '6' end

    + case when m.medium_photo7 is null then '' else '7' end

    + case when m.medium_photo8 is null then '' else '8' end

    + case when m.medium_photo9 is null then '' else '9' end

    From Inserted, [vrebphoto].[dbo].[mls_photo] as m

    where Inserted.id = m.id

    This seemed to work, HOWEVER, now every photo and p_mod_date_time field in the mls table is exactly the same...

  • Jeff Maahs

    Old Hand

    Points: 374

    Ok now. Success everyone!

    --drop trigger photo_numbers

    CREATE TRIGGER photo_numbers

    ON mls_photo

    FOR INSERT , UPDATE

    as

    update [vreb].[dbo].mls set p_mod_date_time=Left(CONVERT( varchar , getdate() , 1),8), photo=

    (case when m.medium_photo is null then '' else '1' end

    + case when m.medium_photo2 is null then '' else '2' end

    + case when m.medium_photo3 is null then '' else '3' end

    + case when m.medium_photo4 is null then '' else '4' end

    + case when m.medium_photo5 is null then '' else '5' end

    + case when m.medium_photo6 is null then '' else '6' end

    + case when m.medium_photo7 is null then '' else '7' end

    + case when m.medium_photo8 is null then '' else '8' end

    + case when m.medium_photo9 is null then '' else '9' end)

    From Inserted, [vrebphoto].[dbo].[mls_photo] as m

    where Inserted.id = [vreb].[dbo].[mls].id

    Thank you everyone!!!

Viewing 14 posts - 16 through 29 (of 29 total)

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