September 17, 2002 at 7:43 am
Gurus,
Two quick questions about triggers:
1. Within a insert,update trigger how can one determine that the event is an insert or an update for us to take actions accordingly(is there something oracle INSERTING, UPDATING?). I don't want to write separate triggers one for insert and one for update because then I will be duplicating some of the codes.
2. Is there a stored procedure or system table that will tell me if a trigger is enabled or disabled?
Thanks in advance.
September 17, 2002 at 8:29 am
You might try writing two triggers, but having the common code in a stored procedure.
This way each trigger can call the stored procedure and pass whether it is an insert or update.
This way you are really not duplicating code. Changes to the stored procedure would in effect change both triggers.
Try sp_helptrigger to get information on the triggers that are enabled.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 17, 2002 at 8:41 am
I was going to suggest the same as Greg with regards to a SP within the trigger.
However, can a trigger be disabled?! Maybe I missed something but I figured a Trigger would always be enabled...
Clive Strong
September 17, 2002 at 8:55 am
Thanks Greg and Clive.
BTW, a trigger can be disabled Clive. Here is the command:
alter table table_name disable trigger trigger_name
September 17, 2002 at 9:00 am
The sp_helptrigger report all triggers irrespective of whether they are enabled or diabled. I would like to know if the trigger is enabled or disabled. Is there a way?
September 17, 2002 at 9:55 am
Here is a total hack on an undocumented field. I've done very little testing, but it seems to work for identifying enabled triggers for this example. Maybe it will also work for you enabled, or disabled triggers. Please let me know if you think this code works. I'd really like to know.
create table x (name char(1))
go
CREATE TRIGGER tx
ON x
FOR DELETE, INSERT, UPDATE
AS
BEGIN
print 'only a test'
END
GO
declare @status int
alter table x disable trigger tx
select @status = status & 2048 from sysobjects where id = object_id('tx')
if @status = 2048
print 'Trigger is disabled'
else
print 'Trigger is enabled'
alter table x enable trigger tx
select @status = status & 2048 from sysobjects where id = object_id('tx')
if @status = 2048
print 'Trigger is disabled'
else
print 'Trigger is enabled'
drop table x
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 17, 2002 at 11:09 am
I aggre with the previous solutions, but here is one way more:
create table xyt ( n varchar(30) null, c int null)
go
create trigger tr_xyt on xyt for delete, insert, update
as
begin
if exists(select * from deleted) and
exists(select * from inserted)
print 'updating a row'
else if exists(select * from deleted)
print 'deleting rows'
else if exists(select * from inserted)
print 'inserting rows'
end
go
insert xyt values ('test insert',4)
go
update xyt set n ='test upd'
quote:
Gurus,Two quick questions about triggers:
1. Within a insert,update trigger how can one determine that the event is an insert or an update for us to take actions accordingly(is there something oracle INSERTING, UPDATING?). I don't want to write separate triggers one for insert and one for update because then I will be duplicating some of the codes.
2. Is there a stored procedure or system table that will tell me if a trigger is enabled or disabled?
Thanks in advance.
September 18, 2002 at 2:56 pm
That works great Greg.
Jorge, I end up using your approach. Thanks guys.
September 18, 2002 at 3:15 pm
Guess that's one more HACK to add to my list. Thank you for the feedback.
[/quote]
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 19, 2002 at 10:05 am
Today I ran across and interesting function "objectproperty". Remember the HACK I wrote to determine if triggers where disabled, well I would suggest you change you code to use the "objectproperty" function. Here is my new code to determine if triggers are enabled, or diabled.
create table x (name char(1))
go
CREATE TRIGGER tx
ON x
FOR DELETE, INSERT, UPDATE
AS
BEGIN
print 'only a test'
END
GO
alter table x disable trigger tx
if objectproperty(object_id('tx'),'execistriggerdisabled') = '1'
print 'Trigger is disabled'
else
print 'Trigger is enabled'
alter table x enable trigger tx
if objectproperty(object_id('tx'),'execistriggerdisabled') = '1'
print 'Trigger is disabled'
else
print 'Trigger is enabled'
drop table x
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply