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))
	select into #deletetriggers from sysobjects so1, sysobjects so2 where ( = so1.deltrig or = so1.instrig or or and
	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
		set @selectString = 'drop trigger '+@trigger_name
		print @selectString
		exec (@selectString)
		fetch delete_trigger_cursor into @trigger_name

	close delete_trigger_cursor
	deallocate cursor delete_trigger_cursor

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. Required fields are marked *