Record locking

[FengShui1998]

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

 

[gmmastros]

You could add a delete trigger to the table.

For example...

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

 

[FengShui1998]

gmmastros,

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

Fengshui1998

 

[gmmastros]

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

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

 

[FengShui1998]

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

 

[markros]

Yes, you can change the trigger this way:

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