Triggers .

Triggers .

Post by Raghu Rajagopala » Thu, 10 Dec 1998 04:00:00



Hi,
  Is there anyway to disable and enable a trigger in Sybase without
dropping and recreating a trigger. In SYBASE 11.5 can the trigger text
be a hidden text.

Regds,
Raghu Rajagopalan.

 
 
 

Triggers .

Post by Bret Halfor » Thu, 10 Dec 1998 04:00:00



> Hi,
>   Is there anyway to disable and enable a trigger in Sybase without
> dropping and recreating a trigger. In SYBASE 11.5 can the trigger text
> be a hidden text.

Yes, in ASE 11.5 a "set triggers {off | on}" command was introduced
that can disable trigger execution.  It was designed for use by
Replication Server and is (obviously) quite dangerous.  A user with SSO
role would have to grant "replication role" to the user who needs to use
this command.  A better (safer) alternative is to have the trigger check
the status of some flag in a table to control which of its logic gets
run.

sp_hidetext does work against triggers.  Do note that sp_hidetext cannot
be reversed.  One you encrypt the code, you can't get it back from the
server.

1> create table mytable (x int)
2> go
1> create trigger mytrigger on mytable for insert as print "hi"
2> go
1> insert mytable values (1)
2> go
hi
(1 row affected)
1> set triggers off
2> go
1> insert mytable values (2)
2> go
(1 row affected)
1> sp_helptext mytrigger
2> go
 # Lines of Text
 ---------------
               1

(1 row affected)

text                                                                                                                                                                                                                                                            

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

         create trigger mytrigger on mytable for insert as print "hi"

(1 row affected)
(return status = 0)
1> sp_hidetext mytrigger
2> go
(return status = 0)
1> sp_helptext mytrigger
2> go
Msg 18406, Level 16, State 1:
Server 'ALLIANCE1', Procedure 'sp_helptext', Line 152:
Source text for compiled object mytrigger (id = 1913777519) is hidden.
(return status = 1)
--
Bret Halford                    Imagine my disappointment
Sybase Technical Support        in learning the true nature            
3665 Discovery Drive            of rec.humor.oracle...
Boulder, CO 80303