Sybase: Drop all triggers on a table

You can use the following stored procedure to drop all triggers on a table:

create procedure drop_all_triggers(@tablename varchar(255))
as
begin
	select so2.name into #deletetriggers from sysobjects so1, sysobjects so2 where (so2.id = so1.deltrig or so2.id = so1.instrig or so2.id=so1.updtrig or so2.id=so1.seltrig) and so1.name=@tablename
	
	declare delete_trigger_cursor cursor
	for select name from #deletetriggers

	declare @trigger_name varchar(255)
	declare @selectString varchar(10000)

	open delete_trigger_cursor
	fetch delete_trigger_cursor into @trigger_name

	while @@sqlstatus = 0
	begin
		set @selectString = 'drop trigger '+@trigger_name
		print @selectString
		exec (@selectString)
		fetch delete_trigger_cursor into @trigger_name
	end

	close delete_trigger_cursor
	deallocate cursor delete_trigger_cursor
end
go

It basically does the following:

  • Fetch the name of all triggers on the table into a temp table (we have to go through a temp table since by dropping a trigger we modifying the sysobject table and it would mess with the cursor.
  • Loop through the trigger names
  • And drop each of them using an execute immediate. Somehow you cannot directly call drop trigger…

Just call it like this:

1> drop_all_triggers mytablename
2> go

Leave a Reply

Your email address will not be published.