how to drop and create Triggers from an other DB?

Hi,
I am running a stored procedure on the database DB01,
that SP got some statement looking like:
use DB02
DROP Trigger DB02_USER.BOOK_DELETE
It does not run and says:
a USE database statement is not allowed in a procedure or trigger.
If I replace the code by
DROP Trigger DB02.DB02_USER.BOOK_DELETE
then it says:
'DROP TRIGGER' does not allow specifying the database name as a prefix to
the object name.
Then how is it possible to drop a trigger in an other DB than the one the SP
is stored in?
Best regards,
Francois Malgreve.> Then how is it possible to drop a trigger in an other DB than the one the
> SP
> is stored in?
EXEC('USE DB02; DROP TRIGGER DB02_USER.BOOK_DELETE')
A|||Thanks a lot for the information.
It works very well.
But I am still faced with a related problem. Creating triggers from an SP.
When I try to execute:
EXEC('USE DB02; CREATE Trigger CSDB_TR_SELLRATE_DELETE
On DB02_USER.BOOK_DELETE
For Delete As
Begin
--code goes here
End
')
I have an error saying:
'CREATE TRIGGER' must be the first statement in a query batch.
Do you have any trick to go around this one?
Thanks,
Francois
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OPFJxy36FHA.2524@.TK2MSFTNGP10.phx.gbl...
the
> EXEC('USE DB02; DROP TRIGGER DB02_USER.BOOK_DELETE')
> A
>