passing variables in trigger - 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

SQL Server 2000 Forums

Transact-SQL (2000)

passing variables in trigger

Author

Topic

Dennis Falls
Starting Member

41 Posts

Posted - 2008-05-28 : 19:07:24

I've searched BOL and several forums and have yet to find a solution to my problem. Hopefully someone can help.

I have 2 tables:

tblEvents(
ID bigint IDENTITY(1,1) NOT NULL,
DataSource varchar(10) NOT NULL,
SyndromeID int NOT NULL,
Location varchar(1000) NOT NULL,
EventBeginDate smalldatetime NOT NULL,
ActionLevel varchar(35) NULL,
ActionLevelID int NOT NULL

tblEventAnnotation(
ID bigint IDENTITY(1,1) NOT NULL,
EventID bigint NOT NULL,
Comments varchar(4000) NULL,
CommentsDateTime datetime NULL,
UserID int NULL

I would like to use 1 stored procedure to populate tblEvents, fire a trigger to populate tblEventAnnotation.EventID from tblEvents.ID and populate the remaining fields in tblEventAnnotation from passed parameters.

The only thing I have found that is close to accomplishing this is

http://www.sqlmag.com/Article/ArticleID/25533/sql_server_25533.html

and I could not get this to work.

I know I can populate tblEventAnnotation.EventID using a trigger then use an update statement using SELECT MAX(ID) FROM tblEvents but I'm concerned that if I have more than 1 user attempting to create an event at the same time could cause problems.

Can this be accomplished using a trigger, if not what would be the best way to insure I am updating the proper EventID?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-28 : 19:12:45

Don't bother with a trigger for this, just put all of the code in the stored procedure. Use SCOPE_IDENTITY() function after the insert into the Event table so that you know what row to insert into the child table.

By the way, it is bad practice to name your column ID. Use EventID in Event table and EventAnnotationID in the Annotation table (or similar of course).

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Dennis Falls
Starting Member

41 Posts

Posted - 2008-05-28 : 19:19:46

Thank you Tara for the quick response. By the way, I appreciate your comment about not using ID as column name. I have argued this point with another developer that created this table for some time now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources