Lock table - 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

Lock table

Author

Topic

psangeetha
Yak Posting Veteran

95 Posts

Posted - 2009-02-04 : 17:02:05

Hi all,

I'm wondering if there is a way to lock a table so we can prevent the child table records from being deleted..

Thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-04 : 17:19:16

A foreign key constraint would only prevent the parent rows from getting deleted if child rows exists and also prevents an orphaned child row from getting inserted if no parent row exists, so we can't use an FK constraint. I guess you'd need to use a trigger with a delete action. A locking hint won't help here.

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

Subscribe to my blog

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-04 : 18:00:02

Like this:

Create Trigger dbo.tr_delete on dbo.table
Instead of DELETE
as
Set Nocount on
If Exists (select 1 from dbo.table m
        Inner Join deleted d on d.pk = s.pk)
Begin
    Raiserror ('Delete not allowed for child Table', 16, 10)
    Rollback
End

psangeetha
Yak Posting Veteran

95 Posts

Posted - 2009-02-05 : 09:45:09

Thanks a lot for the replies.

There is a foreign key constraint on the table CRI to parent table ANJ and the application needs the constraint.

For one program, the specification is written such that the program will throw an error when it is unable to delete a record in ANJ table because the records in CRI are not deleted. To test this, we are trying to force the condition so that the program cannot delete the CRI records and so the delete of ANJ records will fail and the program will throw an error.

Sorry for the confusion.. Thanks again for your help.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-05 : 09:47:35

So Did that work or not?

psangeetha
Yak Posting Veteran

95 Posts

Posted - 2009-02-05 : 10:50:02

On the create trigger, I get this error:

The multi-part identifier "s.messageid" could not be bound.

Thank you

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-05 : 10:52:25

How does your trigger look like?

psangeetha
Yak Posting Veteran

95 Posts

Posted - 2009-02-05 : 10:56:47

It was a typo error in the trigger.Sorry it worked now and when i delete the record in the child table, I get the error that the transaction ended in the trigger.

Thanks a lot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources