?
Solved

Use trigger to validate against a view

Posted on 2006-04-06
3
Medium Priority
?
181 Views
Last Modified: 2008-02-01
I would like to find out if the a part exists when it is entered.  I have a table called Snum and a View called validpart. When an insert is done on the PartNumber field in the Snum table I want it to see if that part exists in the validpart view.  If it does great- if not then error rollback etc...  It's my understand that you cannot create relationship with a view so my only options are either a trigger or constraint.
0
Comment
Question by:dloebig
  • 2
3 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16393490
you can do it in a trigger
but you'd be probably be better off if you did the validation more up front on wether the part was valid...


create trg_ins_snum on table snum
for Insert

as

if not exists (select partno from validpart as v inner join inserted as i on v.partno = i.partno)
begin
    rollback transaction
    raiseerror(.....)
end
else
    ....
end    
0
 

Author Comment

by:dloebig
ID: 16395392
Any idea why it keeps yelling at me about the syntax around "Error"?

CREATE TRIGGER [ValidPart] ON [dbo].[SerialNumber]
FOR INSERT
AS

if not exists (select PartNumber from ValidProduct as v inner join inserted as i on v.PartNumber = i.PartNumber)
begin
    rollback transaction
    raiserror (Error)
End

Else

End
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 2000 total points
ID: 16406784
yes because you need to properly configure the raiserror arguments...

RAISERROR ( { msg_id | msg_str | @local_variable }
    { ,severity ,state }
    [ ,argument [ ,...n ] ] )
    [ WITH option [ ,...n ] ]



e.g.

RAISERROR (N'This is message %s %d.', -- Message text.
           10, -- Severity,
           1, -- State,
           N'number', -- First argument.
           5); -- Second argument.

0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question