Author |
Topic |
diyeys
Starting Member
11 Posts |
Posted - 2007-04-10 : 05:40:43
|
Hello everyone,I am trying to write a trigger but i am stuck.i have 3 tables BLOG, TOPIC AND BLOG_TOPIC. The TOPIC table has a column with name 'insert_date'. The BLOG_TOPIC table has columns 'id_blog' and 'id_topic'. The BLOG table has a column 'last_date'.What i need to do is everytime a topic is updated, then update 'last_date' column with the value of the most recent 'insert_date'.This is what i got so far:CREATE TRIGGER update ON TOPICAFTER INSERT, UPDATE, DELETE ASBEGIN UPDATE M SET M.last_date = T.max_date FROM BLOG M, (SELECT MAX(M1.insert_date) AS max_date FROM TOPIC M1) T WHERE M1.id = (SELECT M2.id_topic FROM BLOG_TOPIC M2 WHERE M2.id_blog = M.id)ENDGOWhat am i doing wrong? Thanks a lot in advance |
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-04-10 : 06:02:50
|
Something like this?CREATE TRIGGER update ON TOPICAFTER INSERT, UPDATE, DELETE ASBEGINUPDATE MSET M.last_date = (select max(insert_date) from inserted i where m.id = i.id)ENDGO Please post complete table structure to get more accurate answers.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-04-10 : 06:19:06
|
Why dont you do it in simple select statement whenever needed than updating that column each time data are added? All you need is, at any point of time,Select max(insert_date) as last_date from tableMadhivananFailing to plan is Planning to fail |
|
|
diyeys
Starting Member
11 Posts |
Posted - 2007-04-11 : 04:24:34
|
Thanks for the responses, i will explain the problem a bit better, this is my tables structure:BLOG TABLE- id- id_author- title- description- most_recent_message_dateMESSAGE TABLE- id- id_group- id_author- insert_date- title- textMESSAGES_GROUP- id- title- description- num_messages- id_stateThe id column in the BLOG table relates with the id column in the MESSAGES_GROUP table.What i need to do is the following, every time a record in the table MESSAGE is inserted, updated, or deleted, i have to update 'most_recent_message_date' column of BLOG table with the most recent insert_date of all the messages of the messages group for that BLOG.Thanks in advance for the help. |
|
|
diyeys
Starting Member
11 Posts |
Posted - 2007-04-11 : 05:46:06
|
Hi again, this is what i got so far, but it seems i cannot make it work...any help?CREATE TRIGGER message_insupdel ON MESSAGEAFTER INSERT, UPDATE, DELETE ASBEGIN DECLARE @inserted int DECLARE @deleted int DECLARE @op int SELECT @inserted = COUNT(*) FROM inserted SELECT @deleted = COUNT(*) FROM deleted IF ((@inserted > 0) AND (@deleted > 0)) SET @op = 2 ELSE IF (@inserted > 0) SET @op = 1 ELSE IF (@deleted > 0) SET @op = 3 ELSE SET @op = 0 IF ((@op = 1) OR (@op = 2) OR (@op = 3)) BEGIN /*-- DELETED --*/ UPDATE M SET M.most_recent_message_date = T.max_date FROM BLOG M, (SELECT MAX(M1.insert_date) AS max_date FROM deleted ID JOIN MESSAGE M1 ON ID.id = M1.id) T WHERE M1.id_group = (SELECT M2.id FROM BLOG M2 WHERE M2.id = MESSAGES_GROUP.id) /*-- INSERTED --*/ UPDATE M SET M.most_recent_message_date = T.max_date FROM BLOG M, (SELECT MAX(M1.insert_date) AS max_date FROM inserted ID JOIN MESSAGE M1 ON ID.id = M1.id) T WHERE M1.id_group = (SELECT M2.id FROM BLOG M2 WHERE M2.id = MESSAGES_GROUP.id) ENDENDGO |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-04-11 : 05:54:11
|
You know that an UPDATE statement produces BOTH inserted and deleted temporary tables?Peter LarssonHelsingborg, Sweden |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-04-11 : 05:56:16
|
It seems to me that this part would be enoughCREATE TRIGGER message_insupdel ON MESSAGEAFTER INSERT, UPDATE, DELETEASUPDATE MSET M.most_recent_message_date = T.max_dateFROM BLOG M, (SELECT MAX(M1.insert_date) AS max_date FROM inserted ID JOIN MESSAGE M1 ON ID.id = M1.id) TWHERE M1.id_group = (SELECT M2.id FROM BLOG M2 WHERE M2.id = MESSAGES_GROUP.id)since all you do is updating the most_recent_message_date column to reflect the last post made, still in the database...Peter LarssonHelsingborg, Sweden |
|
|
diyeys
Starting Member
11 Posts |
Posted - 2007-04-11 : 05:56:50
|
I did not know, thanks you. Apart from that, do you think the trigger is ok? |
|
|
diyeys
Starting Member
11 Posts |
Posted - 2007-04-11 : 05:58:06
|
Thank you , Peso. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-04-11 : 06:41:58
|
This can be usedCREATE TRIGGER message_insupdel ON MESSAGEAFTER INSERT, UPDATE, DELETEASUPDATE bSET b.Most_Recent_Message_Date = m.mdtFROM BLOG AS bINNER JOIN ( SELECT ID_Group, MAX(Insert_Date) AS mdt FROM Message GROUP BY ID_Group ) AS m ON m.ID_Group = b.ID That updates ALL blogs.You can use this to only update the authors who are affected by the INS/UPD/DEL operation.CREATE TRIGGER message_insupdel ON MESSAGEAFTER INSERT, UPDATE, DELETEASUPDATE bSET b.Most_Recent_Message_Date = m.mdtFROM BLOG AS bINNER JOIN ( SELECT ID_Group, MAX(Insert_Date) AS mdt FROM Message GROUP BY ID_Group ) AS m ON m.ID_Group = b.IDINNER JOIN ( SELECT ID_Group FROM inserted UNION SELECT ID_Group FROM deleted ) AS a ON a.ID_Group = b.ID Peter LarssonHelsingborg, SwedenEDIT: Update query to reflect newer information |
|
|
diyeys
Starting Member
11 Posts |
Posted - 2007-04-11 : 06:52:11
|
Thanks Peso, but the author of the message does not have to be the same that the author of the blog. That is why i have to relate them through MESSAGES_GROUP table. 1 BLOG has 1 MESSAGES GROUP, 1 MESSAGES GROUP can have lots of MESSAGES.Thanks again. |
|
|
diyeys
Starting Member
11 Posts |
Posted - 2007-04-11 : 07:13:23
|
Thanks a lot, you have helped, really :) |
|
|
diyeys
Starting Member
11 Posts |
Posted - 2007-04-11 : 09:00:39
|
It works like a charm, thanks. One more thing, Now i also want to add the restriction of 'text' column in MESSAGE table being null:CREATE TRIGGER message_insupdel ON MESSAGEAFTER INSERT, UPDATE, DELETEASUPDATE bSET b.Most_Recent_Message_Date = m.mdtFROM BLOG AS bINNER JOIN ( SELECT ID_Group, text, MAX(Insert_Date) AS mdt FROM Message GROUP BY ID_Group, text ) AS m ON m.ID_Group = b.ID AND a.text IS NULLINNER JOIN ( SELECT ID_Group, text, FROM inserted UNION SELECT ID_Group, text, FROM deleted ) AS a ON a.ID_Group = b.ID AND a.text IS NULLFor any reason it is not updating 'Most_Recent_Message_Date' column.Thanks |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-04-11 : 09:07:51
|
I can't tell of your restriction description. You want the NULL text's to be evaluated?You DON'T want NULL text's to be evaluated?CREATE TRIGGER message_insupdel ON MESSAGEAFTER INSERT, UPDATE, DELETEASUPDATE bSET b.Most_Recent_Message_Date = m.mdtFROM BLOG AS bINNER JOIN ( SELECT ID_Group, MAX(Insert_Date) AS mdt FROM Message WHERE Text IS NOT NULL -- Do not evaluate NULL messages GROUP BY ID_Group ) AS m ON m.ID_Group = b.IDINNER JOIN ( SELECT ID_Group FROM inserted UNION SELECT ID_Group FROM deleted ) AS a ON a.ID_Group = b.ID Peter LarssonHelsingborg, Sweden |
|
|
|