Need to alter current trigger. With delete before insert

[bla1979]

This trigger works perfectly. Unfortunately, I need it to do more, I THINK , I need it to delete the previous insert that this trigger performed first before it inserts the new values.

Here the whole thing.

I have a table called blog_blog, it has the following trigger on it.

CREATE TRIGGER bloglog
ON blog_blog
FOR INSERT 
AS 

DECLARE @find varchar(4)
select @find  = inserted.blog_catagory from inserted

BEGIN 
	INSERT INTO blog_log
	VALUES ('New',DATEADD (hh,+3,GETDATE()),'BLOG',@find);
END ;

GO

I then have a tabled called blog_cat, Which is a topics table. It hold the current topics for my site. It has 2 columns, cat_no, cat_name.
cat_name | cat_no
Photos 1
Videos 2
Random 3


I then run the query below.

Select cat_name, (Select log_status from blog_log where DATEDIFF ( hh ,log_date ,DATEADD(hh , +3, GetDate())) <= 24 and log_blogid = cat_no ) as 'new'
From blog_cat

To show me the topics and if thier has been any blogs posted in the last 24 hours, If yes a 'New' shows up in column new, If not a null shows up, perfect.

This shows that their was a blog posted for photo and random in the last 24 hours, and nothing for video.
TOPICS
PHOTO*
VIDEO
RANDOM*


Problems comes when 2 blogs are posted in the last 24 hours I get the error below.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

So I am under the impression that I should do a delete before I do the trigger insert, to keep just one record.

 

[gmmastros]

I think you are going about this the wrong way. Here's my suggestion:

Alter the blog_cat table by adding a LastUpdated column with data type = DateTime.

Alter Table blog_cat Add LastUpdated DateTime

Then, create your trigger so that the LastUpdated column represents the current date/time of the server.

CREATE TRIGGER bloglog
ON blog_blog
FOR INSERT 
AS 

Update blog_cat
Set    LastUpdated = GetDate()
From   blog_cat
       Inner Join Inserted I
         On blog_cat.blog_catagory = I.blog_catagory

Then, modify your query such that when DateDiff on GetDate() and the LastUpdated column is less than 24 hours, that you show the 'new' status.

Select cat_name, 
       Case When DateDiff(Hour, LastUpdated, GetDate()) < 24
            Then 'New'
            Else 'Old'
            End as 'IsNew'
From blog_cat

I think this approach will simplify your logic. Hope it helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom