need help with trigger

Question

need help with trigger

jeff-825927

Newbie

Points: 2
More actions
July 22, 2008 at 9:33 pm

#191391

to make a long story short we have an application software package that's inserting a bunch of records in a table that do not need to be inserted. it's a compiled product that we do not have the source code to so we cannot change it's behavior so I wrote a trigger that checks the table for records with values in two of the fields and then deletes the record.
it's working ok but it's a clunky piece of code that is far from elegant.
here's what it currently look like:
create trigger nukespam on [dbo].[inmail]
for insert
as
delete [dbo].[inmail] where HdrTo like '%bounce-%' and List is NULL
there's another field in the table called MessageID and I'd prefer the trigger select the record with the highest MessageID and select just that one record, test the fields and delete the record if the HdrTo field and List fields meet the criteria for deletion.
can someone give me a hand developing this trigger?
thanks in advance.

Viewing 5 posts - 1 through 5 (of 5 total)

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

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 1

Jeff,

you can achieve this by making a sub query followed to you another delete statement or same delete dml as per your requirements like this:

where MessageID
in
(select max(MessageID ) from inmail
where HdrTo like '%bounce-%' and List is NULL)

hope it will help you..

Cheers!

Sandy.

--

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

Is the row with the highest messageID the one that's just been inserted? If so, you can find it in the inserted table.

Perhaps something like this?

create trigger nukespam on [dbo].[inmail]

for insert

as

delete [dbo].[inmail] where MessageID in (select MessageID from inserted)

and HdrTo like '%bounce-%' and List is NULL

If multiple messages get inserted in one insert statement, the inserted table will have more than one row.

Personally, I'd suggest an instead of trigger. Doing the delete in an after trigger (which is what this is) means that all the constraints get checked, the insert gets done and logged then certain records get deleted and logged With an instead of trigger you intercept the insert and can control what rows actual go into the table. Here's a rough, quick example

create trigger nukespam

on [dbo].[inmail]

instead of insert

AS

INSERT INTO inmail (<column list here> )

SELECT <column list here> FROM inserted WHERE HdrTo NOT LIKE '%bounce-%' and List IS NOT NULL

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jeff-825927 Newbie Points: 2 More actions · Answer 3

GilaMonster (7/22/2008)
Is the row with the highest messageID the one that's just been inserted? If so, you can find it in the inserted table.
Perhaps something like this?
create trigger nukespam on [dbo].[inmail]
for insert
as
delete [dbo].[inmail] where MessageID in (select MessageID from inserted)
and HdrTo like '%bounce-%' and List is NULL
If multiple messages get inserted in one insert statement, the inserted table will have more than one row.
Personally, I'd suggest an instead of trigger. Doing the delete in an after trigger (which is what this is) means that all the constraints get checked, the insert gets done and logged then certain records get deleted and logged With an instead of trigger you intercept the insert and can control what rows actual go into the table. Here's a rough, quick example
create trigger nukespam
on [dbo].[inmail]
instead of insert
AS
INSERT INTO inmail (<column list here> )
SELECT <column list here> FROM inserted WHERE HdrTo NOT LIKE '%bounce-%' and List IS NOT NULL

thank you - this worked great!

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

Pleasure

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass