Trigger Cegah Drop Table di SQL Server 2005

Oleh Achmad Faisol

Seringkali kita menginginkan agar table-table di database tidak bisa didrop. Hal ini untuk menghindari ketidaksengajaan atau kesalahan perintah yang berakibat terhapusnya table.

Di artikel ini akan dibuat sebuah trigger di level database yang berguna untuk mencegah agar table-table di database tersebut tidak bisa dihapus (di-drop).

Misal kita punya database “sistra” dengan beberapa table di dalamnya.

1. Buka Query

use sistra
go
select * from sysobjects where xtype='u'
and name not like '%sys%'

-- menampilkan table-table di database sistra

2. Buat trigger di database “sistra” untuk mencegah agar table-table di dalamnya tidak bisa didrop.

use sistra
go
CREATE TRIGGER PreventDropTable
ON DATABASE
FOR DROP_TABLE
AS
PRINT 'Table tidak bisa dihapus!'
ROLLBACK
go

-- perintah ROLLBACK itulah yang akan mencegah sebuah table di drop

3. Tes dengan menghapus salah satu table, misal table pegawai.

use sistra
go
drop table pegawai
go

-- terjadi error dan muncul pesan:

Table tidak bisa dihapus!
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.



4. Bila sebuah table ingin dihapus, maka disable dulu trigger PreventDropTable, lalu hapus table yang diinginkan, misal table pegawai.

--- disable trigger
DISABLE TRIGGER PreventDropTable
ON DATABASE
go

-- hapus table
drop table pegawai
go -- sukses

--- enable trigger
ENABLE TRIGGER PreventDropTable
ON DATABASE
go

CATATAN : JANGAN LUPA untuk meng-enable kembali trigger tersebut.


5. Jika diinginkan hanya table tertentu saja yang tidak bisa dihapus, misal hanya table COBA2, maka harus ada kondisi di trigger

use sistra
go
create table coba1 (f1 varchar(10))
go
create table coba2 (f1 varchar(10))
go

CREATE TRIGGER PreventDropTable
ON DATABASE
FOR DROP_TABLE
AS

Declare
@nama_tabel varchar(50)
Select @nama_tabel =
EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)')

If @nama_tabel = 'coba2'
begin
PRINT 'Table coba2 tidak bisa dihapus!'
ROLLBACK
end
go

drop table coba2
go
-- error

drop table coba1
go
-- sukses