November 13, 2001 at 8:32 am
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
November 13, 2001 at 11:53 am
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
November 13, 2001 at 1:07 pm
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?
November 13, 2001 at 1:54 pm
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
Paul.Ibison@replicationanswers.com
November 13, 2001 at 2:56 pm
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!
November 13, 2001 at 7:25 pm
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.
November 14, 2001 at 5:24 am
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
Paul.Ibison@replicationanswers.com
November 14, 2001 at 8:11 am
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!
November 14, 2001 at 10:23 am
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.
November 14, 2001 at 10:37 am
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.
November 14, 2001 at 12:14 pm
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.
November 14, 2001 at 12:48 pm
Ah,
vreb.dbo.mls
vrebphoto.dbo.mls_photo
I suppose this makes a difference.
November 14, 2001 at 1:18 pm
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...
November 14, 2001 at 1:36 pm
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 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply