Insert Trigger and Raiserror??

Question

Is this idea feasible:

An INSERT Trigger that checks a string for valid values, and using RAISERROR to notify the user their input is incorrect.

Example: user enters 'Maybe' into a field that can only be 'Yes' or 'No'. The INSERT Trigger will not allow the value to be input and display a message that says: 'Please enter Yes or No'.

Thanks!!!  Linda

---

Linda

Answer 1

To me it sounds like you want a check constraint; can you add additional details to what you have in mind?

Answer 2

Feasible? Somehow. For defining domain constraints you would use either CHECK constraints or the Domain Key Normal Form.

Using a trigger it would be really the last option...

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

Answer 3

Linda,

As others pointed out, this particular problem is better handled with the CHECK CONSTRAINT. The only minor inconvenience is that the error message will be SQL Server generated message and not a custom message. I'm wondering if there is a Connect item already to add ability to specify custom error messages for the constraints.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 4

hi Linda, Naomi,

as end users should never work with tables directly, I also think that end users should also never see database generated error messages. Neither built-in nor custom ones.

When there is a need for a database-side intermediate layer, it should be handled by stored procedures. In this cases I would consider Defensive Programming as the way to go. So in the sense of Secure input and output handling: check values before you apply them against your table(s).

Just my 2 cents.

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

Answer 5

That's true as well - we can have a simple check first filtering bad output on the client, but we still need to have this check constraint. It should prevent the ways of bypassing the client app check.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 6

Thanks everyone!!! I am looking at Check Constraints. I don't see a way to have a condition in the CHECK constraint. I would want it to be like:

Table (Field varchar(35), Data varchar(35))

ALTER

TABLE [dbo].[Table]

ADD

CONSTRAINT chk_Status CHECK (CASE WHEN Field='PATIENT' THEN DATA='5' WHEN Field = 'NURSE' THEN DATA IN ('4','6','7') ELSE END)

Is something like this possible???

THANKS!

 

---

Linda

Answer 7

CONSTRAINT chk_StatusCHECK(CASEWHEN Field='PATIENT'THEN DATA='5' WHEN Field = 'NURSE' THEN DATA IN ('4','6','7') ELSEEND)
Is something like this possible???

No. This is either a real (functional) dependency or much worse you're mixing two or more entities: Patients and employees (nurses, doctors). In both cases you need to reconsider your data model (normalization).

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

Answer 8

I agree with Stefan - looks like you're trying to overload the single field (somewhat of EAV model). Don't do this - go back to database design.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 9

And I second (third?) Stefan and Naomi.  There are a very few cases where EAV designs are appropriate, but this doesn't sound to me like one of them.  In the long run you will be happier and more successful with a different design.

Having counseled not to shoot yourself in the foot, I will now offer you a gun.  You can't code that constraint the way you wrote it, but you could do

Create Table dbo.Foo(Field varchar(10), Data char(1));
ALTER TABLE dbo.Foo
ADD CONSTRAINT chk_Status CHECK (CASE WHEN Field='PATIENT' AND DATA='5' THEN 1 WHEN Field = 'NURSE' AND DATA IN ('4','6','7') THEN 1 ELSE 0 END = 1)
-- This works
Insert dbo.Foo(Field, Data) Values ('PATIENT', '5');
-- This works
Insert dbo.Foo(Field, Data) Values ('NURSE', '4');
-- This fails
Insert dbo.Foo(Field, Data) Values ('PATIENT', '4');
-- This fails
Insert dbo.Foo(Field, Data) Values ('NURSE', '5');
-- This fails
Insert dbo.Foo(Field, Data) Values ('BAD', '0');
-- check which rows were successfully inserted
Select * From dbo.Foo
-- result
-- PATIENT	5
-- NURSE	4
go
Drop Table dbo.Foo

Tom

Answer 10

I totally agree with all of you! However, the database design is not in my control. Until it gets fixed, we are trying to figure out how to stop bad data from getting into the database.

I am trying to add Tom's gun idea to my table since I am kind of out of good sensible options but am getting the follwoing below. I can't tell what is conflicting. Does anyone know?

Msg 547, Level 16, State 0, Line 2

The ALTER TABLE statement conflicted with the CHECK constraint "chk_P". The conflict occurred in database "IcsDemo_NJ", table "dbo.USER_DEF_DATA".

CREATE

TABLE [dbo].[USER_DEF_DATA]

(

[DATA_FIELD_NAME] [varchar]

(32) NOT NULL, 

[KEY_VALUE] [varchar]

(20) NOT NULL,

[DATA_VALUE] [varchar]

(255) NOT NULL,

 

CONSTRAINT [PK_USER_DEF_DATA_1] PRIMARY KEY CLUSTERED

(

[KEY_VALUE]

ASC,

[DATA_FIELD_NAME]

ASC

)

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

)

ON [PRIMARY]

GO

SET

ANSI_PADDING

OFF

GO

ALTER

TABLE [dbo].[USER_DEF_DATA] WITH NOCHECK ADD CONSTRAINT [FK_USER_DEF_DATA_USER_DEF_METADATA] FOREIGN KEY([DATA_FIELD_NAME]

)

REFERENCES

[dbo].[USER_DEF_METADATA] ([DATA_FIELD_NAME]

)

NOT

FOR REPLICATION

GO

ALTER

TABLE [dbo].[USER_DEF_DATA] CHECK CONSTRAINT [FK_USER_DEF_DATA_USER_DEF_METADATA]

GO

ALTER

TABLE [dbo].[USER_DEF_DATA] WITH CHECK ADD CONSTRAINT [chk_P]

CHECK

 

((case when [Data_Field_Name]='PATIENT_STATUS' AND [DATA_VALUE]='S' then (1) when [Data_Field_Name]='NURSE' AND ([DATA_VALUE]='7' OR [DATA_VALUE]='6' OR [DATA_VALUE]='4') then (1) else (0) end=(1

)))

GO

ALTER

TABLE [dbo].[USER_DEF_DATA] CHECK CONSTRAINT [chk_P]

---

Linda

Answer 11

The error you're getting means you already have bad data in the table that violates your constraint expression. You need to first find these records and fix the problems before you will be able to apply the constraint.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 12

Thanks Naomi. That makes sense. There is a lot of bad data! 

THANKS TO EVERYONE for you valuable input!

---

Linda