Trigger problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trigger problem

Author

Topic

kitty2604
Starting Member

4 Posts

Posted - 2009-09-29 : 11:19:09

I am pretty much new to SQL Server triggers.

I have 2 tables. on the after insert/update trigger of table1. i am inserting a row into table 2.

But the table 2 has a before insert trigger in which i am checking a condition, that either of the columns should have a value and not both.

if this fails i am raising an exception, and rollback transaction.

But even when I insert a good record without violating this condition, that row never gor inserted into table 2.

Following is my trigger on table 1

ALTER TRIGGER [dbo].[trg_Table1]
ON [dbo].[Table1]
AFTER INSERT,UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

INSERT INTO Table2
(
[EVENT_ID]
,[CRITICAL_EVENT_ID]
,[EVENT_SCHEDULE_STATUS_TYPE_ID]
,[DESCRIPTION]
,[EVENT_SCHEDULE_DATE]
,[EVENT_DUE_DATE]
,[ACTIVE_IND]
,[CREATION_DT_TM]
,[CREATION_USER]
,[LAST_MODIFIED_DT_TM]
,[LAST_MODIFIED_USER])
Select
null
,CRITICAL_EVENT_ID
,4 --null--EVENT_SCHEDULE_STATUS_TYPE_ID 1,2,3,
,AF_DESCRIPTION
,EVENT_TIMESTAMP
,getdate() --EVENT_DUE_DATE
,'Y' --Active_Ind Y,N
,CREATION_DT_TM
,CREATION_USER
,LAST_MODIFIED_DT_TM
,LAST_MODIFIED_USER from inserted

END

the following is triiger on table2

ALTER TRIGGER [dbo].[trg_Table2]
ON [dbo].[Table2]
instead of INSERT,UPDATE
AS
declare @lv_event_id integer,
@lv_critical_event_id integer;

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for trigger here
select @lv_event_id = event_id
,@lv_critical_event_id = critical_event_id from INSERTED

-- Check if both event ids are null
IF @lv_event_id is null and @lv_critical_event_id is null
BEGIN
RAISERROR ('Both event_Id and critical_event_id can not be null', 16, 1)
ROLLBACK TRANSACTION
END
--Check if both event ids have values
else IF @lv_event_id is not null and @lv_critical_event_id is not null
BEGIN
RAISERROR ('Both event_Id and critical_event_id can not have values', 16, 1)
ROLLBACK TRANSACTION
END
END

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-09-29 : 12:10:26

The problems are with the trigger on Table2:
1. It is an instead of trigger with no insert into Table2
2. It will only work with a non-deterministic single row if multiple rows are inserted.

I would be inclined to ditch the trigger and use a CHECK constraint.


ALTER TABLE dbo.Table2
DROP TRIGGER dbo.trg_Table2

ALTER TABLE dbo.Table2
ADD CONSTRAINT CK_Table2_Event_Critical_Event
	CHECK
	(
		(event_id IS NULL AND critial_event_id IS NOT NULL)
		OR
		(event_id IS NOT NULL AND critial_event_id IS NULL)
	)

p.s. A more fundamental problem is the design of Table2.
I would suggest only having a single event_id and an IsEventCritical flag in either Table2 or a separate events table.

kitty2604
Starting Member

4 Posts

Posted - 2009-09-29 : 13:38:19

Thank you for your help on this.

I appreciate it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources