Trigger trouble - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Trigger trouble

Author

Topic

jbezanson
Starting Member

35 Posts

Posted - 2005-05-10 : 12:52:41

Hi I have been writting a trigger for one of my tables and can't seem to get the results I want. Let me mention I am very new to TSQL.

Here is the table columns.

Form_ID, ListBox_ID, Label, Priority, Alignment, Email

The Email column is the focus of the trigger. The column can be either yes or no. What I want is a way to allow only one Email with the value of yes for ListBox_ID's witht he same Form_ID.

Basically if the Form_ID's are the same only one Email can = yes.

Here is what I have so far.

CREATE TRIGGER [limitEmail] ON [dbo].[Form_List_Box] 
FOR INSERT, UPDATE
AS
IF EXISTS(SELECT * FROM Form_List_Box WHERE Email = "yes")
RAISERROR(50001,16,10)
ROLLBACK TRANSACTION

This only allows one Email to = yes for the WHOLE table but I want ti to be only for Form_ID's that are the same.

Thanks for any help.

Justin

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-05-10 : 13:41:06

Instead of storing the information is that table, create a new table, and store the info there. Since the primary key is Form_ID, you would only allow one per Form_ID. You should create a foreign key reference to [dbo].[Form_List_Box] to ensure the row exists.

create table Form_Email
(
Form_ID		int not null
	primary key clustered
ListBox_ID	int not null
)

CODO ERGO SUM

mpetanovitch
Yak Posting Veteran

52 Posts

Posted - 2005-05-10 : 15:57:34

True, you should look at your design. Rule of thumb is to stay away from triggers if at all possible.

In case you can't change the schema.


CREATE TRIGGER [limitEmail] ON [dbo].[Form_List_Box] 
FOR INSERT, UPDATE
AS
------------------------------
--grab data from inserted table
------------------------------
DECLARE @email varchar(5), @ListBox_ID int
SELECT @email = i.email, @ListBox_ID = i.ListBox_ID
FROM inserted

------------------------------
--run check
------------------------------
IF (@email = 'yes')
BEGIN
     IF EXISTS(SELECT 1 FROM Form_List_Box
                      WHERE ListBox_ID = @ListBox_ID
                            AND email = 'yes')
     BEGIN 
          RAISERROR(50001,16,10)
          ROLLBACK TRANSACTION
     END
END

Mike Petanovitch

jbezanson
Starting Member

35 Posts

Posted - 2005-05-11 : 15:24:39

thanks guys i got it going now

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources