Table locking... - 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)

Table locking...

Author

Topic

Harry C
Posting Yak Master

148 Posts

Posted - 2005-07-27 : 19:15:21

CREATE TRIGGER trgInsertProject

ON Project

FOR INSERT

AS
SET NOCOUNT ON
DECLARE @ProjectID int
DECLARE @SeasonID int
DECLARE @ProductCategoryID int
DECLARE @CompanyID int
DECLARE @ShipDate datetime
DECLARE my_cursor CURSOR STATIC FOR
SELECT ShipDate,
ISNULL(CompanyID, -1) AS CompanyID,
ISNULL(SeasonID, -1) AS SeasonID,
ISNULL(ProductCategoryID, -1) AS ProductCategoryID,
ProjectID
FROM Project
WHERE DateCreated>=DATEADD(s,-5,GetDate())

OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @ShipDate, @CompanyID, @SeasonID, @ProductCategoryID, @ProjectID
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC pCreateProjectEvents @ShipDate, @CompanyID, @SeasonID, @ProductCategoryID, @ProjectID

FETCH NEXT FROM my_cursor INTO @ShipDate, @CompanyID, @SeasonID, @ProductCategoryID, @ProjectID
END
CLOSE my_cursor
DEALLOCATE my_cursor

GO

Harry C
Posting Yak Master

148 Posts

Posted - 2005-07-27 : 19:16:49

Forgot the question above...

How can I ensure that this trigger fires for ONLY the ProjectIDs that were in the transaction? I mean, what if two user insert at 5 seconds apart. According to my code, there is a chance that they may run into one another, and both set of IDs will get called from within BOTH transactions!! Is there any way to appropriately lock the table, so that it is only released AFTER the stored proc runs AND the trigger is called for the appropriate ProjectIDs? Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-27 : 19:18:17

You need to use the inserted table instead of Project. Check SQL Server Books Online CREATE TRIGGER for information on inserted and deleted tables. These tables exists while in the trigger.

Tara

Harry C
Posting Yak Master

148 Posts

Posted - 2005-07-27 : 19:21:26

quote:
Originally posted by tduggan

You need to use the inserted table instead of Project. Check SQL Server Books Online CREATE TRIGGER for information on inserted and deleted tables. These tables exists while in the trigger.

Tara

But I think I need to lock BOTH tables...right? The data I am selecting FROM is the Project table, the data I am inserting TO is the ProjectEvent table. So what if two users insert records within seconds of each other? How do I lock the records so what I don't run into problems? Or am I way off base here. Thanks Tara

HC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-27 : 19:24:20

No, you don't lock Project. The inserted table contains the rows that are being inserted. You do not need to go to the Project table to find out what was just inserted. That's what the inserted table is for!

Tara

Harry C
Posting Yak Master

148 Posts

Posted - 2005-07-27 : 20:53:08

quote:
Originally posted by tduggan

No, you don't lock Project. The inserted table contains the rows that are being inserted. You do not need to go to the Project table to find out what was just inserted. That's what the inserted table is for!

Tara

deleted and inserted are logical (conceptual) tables. They are structurally similar to the table on which the trigger is defined, that is, the table on which the user action is attempted, and hold the old values or new values of the rows that may be changed by the user action.

So, change my Trigger to
CREATE TRIGGER trgInsertProject

ON Project

FOR INSERT

AS
SET NOCOUNT ON
DECLARE @ProjectID int
DECLARE @SeasonID int
DECLARE @ProductCategoryID int
DECLARE @CompanyID int
DECLARE @ShipDate datetime
DECLARE my_cursor CURSOR STATIC FOR
SELECT ShipDate,
ISNULL(CompanyID, -1) AS CompanyID,
ISNULL(SeasonID, -1) AS SeasonID,
ISNULL(ProductCategoryID, -1) AS ProductCategoryID,
ProjectID
FROM inserted

OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @ShipDate, @CompanyID, @SeasonID, @ProductCategoryID, @ProjectID
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC pCreateProjectEvents @ShipDate, @CompanyID, @SeasonID, @ProductCategoryID, @ProjectID

FETCH NEXT FROM my_cursor INTO @ShipDate, @CompanyID, @SeasonID, @ProductCategoryID, @ProjectID
END
CLOSE my_cursor
DEALLOCATE my_cursor

That seems too easy...but works like a charm! Thanks Tara!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources