Use trigger to validate against a view
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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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