Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Record locking

Status
Not open for further replies.
Joined
Apr 27, 1999
Messages
705
Location
US
Hello,

I am doing an INSERT on a table that creates a RecordID. After the INSERT, I would like to prohibit anyone from deleting that record, but still be able to UPDATE it.

Is there a way to DISABLE DELETE or something?

Thanks ahead.

Fengshui1998
 
You could add a delete trigger to the table.

For example...

Code:
Create Trigger tgr_[!]YourTableNameHere[/!]
	On [!]YourTableNameHere[/!]
	For Delete
AS
	RaisError ('You cannot delete data from the [!]YourTableNameHere[/!] table',16,1);

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
gmmastros,

Thanks for your reply. It seems that only displays a message. What keeps the record(s) from actually being deleted?

Fengshui1998
 
I made a mistake, sorry. The actual sample code is...

Code:
Create Trigger tgr_YourTableNameHere
  On YourTableNameHere
  [!]Instead Of Delete[/!]
AS
  RaisError ('You cannot delete data from the YourTableNameHere table',16,1);

Instead of FOR DELETE, you need to use INSTEAD OF DELETE.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 


Is there a way to create the trigger for a certain RecordID?

 
Yes, you can change the trigger this way:
Code:
create table tr_YourTableName_KeepParticularID
AFTER DELETE on YourTableName
AS
declare @RecordID int
set @RecordID = 5 -- my record ID to keep
IF exists (select 1 from Deleted where RecordID = @RecordID)
  begin
   raiserror('You can not deleted this Record ID %d',16,1,@RecordID)

    rollback
  end

PluralSight Learning Library
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top