Should I use an insert trigger?

Question

Post reply

Should I use an insert trigger?

mike 57299

SSCrazy

Points: 2529
More actions
May 21, 2010 at 12:09 pm

#386986

Hi All,
I have a question. I have a table called messaging_queue.
CREATE TABLE [dbo].[messaging_template](
[messaging_template_id] [dbo].[primary_key_id] IDENTITY(1,1) NOT NULL,
[messaging_area] [char](30) NULL,
[messaging_name] [dbo].[name] NULL,
[messaging_description] [char](200) NULL,
[messaging_department] [dbo]. NULL,
[messaging_html] [varchar](max) NULL,
[messaging_notes] [varchar](max) NULL,
[messaging_filename] [dbo].[filename] NULL,
[messaging_pgm] [dbo].[pgm] NULL,
[last_used_dt] [datetime] NULL,
CONSTRAINT [PK_messaging_template] PRIMARY KEY NONCLUSTERED
(
[messaging_template_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The website puts in new record in the table. I want to fire off an EXE when that happens. I have discussed using the insert trigger. One concern is locking. Will the website be able to insert multiple records and spin off exe's as needed?
Any ideas on ways to accomplish this task without an insert trigger?
Thanks,
Mike

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lamprey13 SSCertifiable Points: 6190 More actions · Answer 1

I'm not sure if that is possible. But, even if it is possible, that sounds like a really bad idea.

Have you investigated using a "listener" app that reads unprocessed rows from the table and calls the Exe?

mike 57299 SSCrazy Points: 2529 More actions · Answer 2

Lamprey13 (5/21/2010)
I'm not sure if that is possible. But, even if it is possible, that sounds like a really bad idea.
Have you investigated using a "listener" app that reads unprocessed rows from the table and calls the Exe?

It is possible. I am doing it now. I am just worried about resources, locks, etc. As for the "listener" app, I don't have experience with it. Any ideas on where to look, what to write it in, examples?

Thanks,

Mike

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 3

mike 57299 (5/21/2010)
Lamprey13 (5/21/2010)
I'm not sure if that is possible. But, even if it is possible, that sounds like a really bad idea.
Have you investigated using a "listener" app that reads unprocessed rows from the table and calls the Exe?
It is possible. I am doing it now. I am just worried about resources, locks, etc. As for the "listener" app, I don't have experience with it. Any ideas on where to look, what to write it in, examples?
Thanks,
Mike

As was previously stated, this is probably a bad idea. Your inserts are now tied to running those EXE's and if they fail your insert fails. It is even possible that this could potentially crash SQL Server.

I would recommend that you look into Service Broker. Basically, with a service broker you setup a queue, the insert trigger will add rows to the queue and the service broker processes those using your exe's.

I have not implemented this myself, but others around here have - hopefully they'll jump in and give you some pointers.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs