Help With a Trigger, please - SQL Server Forums

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

Help With a Trigger, please

Author

Topic

stephenbaer
Yak Posting Veteran

71 Posts

Posted - 2008-07-30 : 14:24:28

I'm try to craft an AFTER INSERT trigger that waits a few seconds for the insert of a record in related table.
Here is a sample, with the create scripts
----------------
First table:
----------------
CREATE TABLE [dbo].[test1](
[Test_1_ID] [int] IDENTITY(1,1) NOT NULL,
[TestData] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_test1] PRIMARY KEY CLUSTERED
(
[Test_1_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
----------------
Second Table:
----------------
CREATE TABLE [dbo].[Test2](
[Test_2_ID] [int] IDENTITY(1,1) NOT NULL,
[Test_1_ID] [int] NOT NULL,
[Related_Value] [varchar](50) NOT NULL,
CONSTRAINT [PK_Test2] PRIMARY KEY CLUSTERED
(
[Test_2_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
USE [TestDatabase]
GO
ALTER TABLE [dbo].[Test2] WITH CHECK ADD CONSTRAINT [FK_Test2_test1] FOREIGN KEY([Test_1_ID])
REFERENCES [dbo].[test1] ([Test_1_ID])

--------------
Insert trigger on test1:
--------------
CREATE TRIGGER Wait_For_Related
ON Test1
AFTER INSERT
AS
BEGIN
WHILE (SELECT COUNT (*)
FROM test2
WHERE test_1_ID = inserted.Test_1_ID) < 1
CONTINUE
Print 'related record inserted'

END
--------------
The create trigger returns:
--------------
"Msg 4104, Level 16, State 1, Procedure Wait_For_Related, Line 11
The multi-part identifier "inserted.Test_1_ID" could not be bound."
--------------
Is what I'm trying to do even possible? Is there a better way?
The final result is a datbasemail that returns the inserted(new) record, as well as the related record.
See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107043
for an example, that SP runs as a job every day at 11:59pm. I'd like the email to trigger immediately, though.

Thank you!

----------------
-Stephen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-30 : 14:26:25

You need to join to the inserted table. You can't just refer to it until you've added it to the query.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

stephenbaer
Yak Posting Veteran

71 Posts

Posted - 2008-07-30 : 14:31:53

<blush> Duh. Ok, let me try this again. Thanks for the quick reply. I see I'm posting in the right forum, huh?

----------------
-Stephen

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-30 : 14:32:02

Didnt understand how it will wait until second table gets inserted.The trigger gets fired just after you insert into test1. then how will you will be inserting into test2. or is it that you insert into test2 first?i'm still not clearly your purpose behind this condition check.

stephenbaer
Yak Posting Veteran

71 Posts

Posted - 2008-07-30 : 15:06:56

Ummm...I don't quite understand it myself. I just got the trigger to fire, with the join, but it just hangs up in what is apparently an infinite loop, waiting to commit data while the related record fails to insert due to the constraint, which is still waiting for the commit of the first... sigh.
The purpose is as follows: We run several adolescent facilities. When we get a new client, the client is entered into the client table. In addition, each client has an identifier, provided by the placing agency. I keep this identifier in a separate table, because the placing agencies have different types of identifiers, so I'd need to have columns in the client table for identifiers that may or may not apply to that particular client. The "new client" form on the front end has the required field for the client table, and an identifier type, as well as identifier number. The submit button on the form inserts one, then the other, sequentially, to avoid constraint violations. Then it generates an Outlook email, notifying interested parties of the new record.
The problem is that Outlook has proven unreliable if any incomplete messages exist in the drafts folder. To avoid this, I'd like to have database mail generate the notification. It does, right now, but it does it as a scheduled job. (yes, currently it sends two, one from the front end immediately, and one from database mail at midnight, just in case the outlook one failed. The problem is that since the job runs at midnight, none of the interested parties receive Friday's new records until Monday morning. With teenagers, a lot can happen between Tursday night and Monday night... Basically, I want instant notification of in insert to the client table, but that can't go out until the insert of the identifier in the identifiers table. Does that make sense?

----------------
-Stephen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-30 : 15:16:19

Why don't you just handle everything through a stored procedure?

Insert1
Insert2
Send email via Database Mail

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

stephenbaer
Yak Posting Veteran

71 Posts

Posted - 2008-07-30 : 15:22:00

Ah, there's the real trouble, of course. The front end is Access, via ODBC, so I can't execute a SP, or at least I can't figure out how... I'm slowly trying to learn enough ASP to convert it.

----------------
-Stephen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources