Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 trigger

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 TOPIC
AFTER INSERT, UPDATE, DELETE AS
BEGIN

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)

END
GO

What 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 TOPIC
AFTER INSERT, UPDATE, DELETE AS
BEGIN

UPDATE M
SET M.last_date = (select max(insert_date) from inserted i where m.id = i.id)

END
GO


Please post complete table structure to get more accurate answers.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

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 table

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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_date

MESSAGE TABLE

- id
- id_group
- id_author
- insert_date
- title
- text

MESSAGES_GROUP

- id
- title
- description
- num_messages
- id_state

The 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.
Go to Top of Page

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 MESSAGE
AFTER INSERT, UPDATE, DELETE AS
BEGIN

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)

END

END
GO
Go to Top of Page

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 Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-11 : 05:56:16
It seems to me that this part would be enough

CREATE TRIGGER message_insupdel ON MESSAGE
AFTER INSERT, UPDATE, DELETE
AS

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)

since all you do is updating the most_recent_message_date column to reflect the last post made, still in the database...


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

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?
Go to Top of Page

diyeys
Starting Member

11 Posts

Posted - 2007-04-11 : 05:58:06
Thank you , Peso.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-11 : 06:41:58
This can be used
CREATE TRIGGER message_insupdel ON MESSAGE
AFTER INSERT, UPDATE, DELETE
AS

UPDATE b
SET b.Most_Recent_Message_Date = m.mdt
FROM BLOG AS b
INNER 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 MESSAGE
AFTER INSERT, UPDATE, DELETE
AS

UPDATE b
SET b.Most_Recent_Message_Date = m.mdt
FROM BLOG AS b
INNER JOIN (
SELECT ID_Group,
MAX(Insert_Date) AS mdt
FROM Message
GROUP BY ID_Group
) AS m ON m.ID_Group = b.ID
INNER JOIN (
SELECT ID_Group
FROM inserted

UNION

SELECT ID_Group
FROM deleted
) AS a ON a.ID_Group = b.ID

Peter Larsson
Helsingborg, Sweden

EDIT: Update query to reflect newer information
Go to Top of Page

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.
Go to Top of Page

diyeys
Starting Member

11 Posts

Posted - 2007-04-11 : 07:13:23
Thanks a lot, you have helped, really :)
Go to Top of Page

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 MESSAGE
AFTER INSERT, UPDATE, DELETE
AS

UPDATE b
SET b.Most_Recent_Message_Date = m.mdt
FROM BLOG AS b
INNER 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 NULL
INNER 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 NULL

For any reason it is not updating 'Most_Recent_Message_Date' column.

Thanks
Go to Top of Page

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 MESSAGE
AFTER INSERT, UPDATE, DELETE
AS

UPDATE b
SET b.Most_Recent_Message_Date = m.mdt
FROM BLOG AS b
INNER 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.ID
INNER JOIN (
SELECT ID_Group
FROM inserted

UNION

SELECT ID_Group
FROM deleted
) AS a ON a.ID_Group = b.ID

Peter Larsson
Helsingborg, Sweden
Go to Top of Page
   

- Advertisement -