September 11, 2012 at 9:25 am
my delivery entry screen shows
referenceid deliverydate deliverysttime deliveryendtime
1 2012-09-11 16:14 17:05
when they try to insert the second record with same referenceid ,trigger comes into action and it has to show no insertion pls referenceid should be unique
i created one but its not working
create TRIGGER TRIG_DEL
ON deliveryEntry
FOR INSERT
AS
BEGIN
declare @cut int
SELECT @cut= i.delReference FROM deliveryEntry AS i
left join deliveryEntry de on
i.delReference=de.delReference
where de.delReference=@cut
print'no insertion available'
END
cheers
September 11, 2012 at 10:02 am
vinay.varaala (9/11/2012)
my delivery entry screen shows
referenceid deliverydate deliverysttime deliveryendtime
1 2012-09-11 16:14 17:05
when they try to insert the second record with same referenceid ,trigger comes into action and it has to show no insertion pls referenceid should be unique
i created one but its not working
create TRIGGER TRIG_DEL
ON deliveryEntry
FOR INSERT
AS
BEGIN
declare @cut int
SELECT @cut= i.delReference FROM deliveryEntry AS i
left join deliveryEntry de on
i.delReference=de.delReference
where de.delReference=@cut
print'no insertion available'
END
cheers
There are a number of issues with the code you posted.
You declare @cut but you try to assign it and use in the same query. This is the same as "where de.delReference is null" because @cut is null at that point.
Being that this is a trigger you should be looking at the inserted table for inserts. When you reference the base table your new row is not yet there so it will never find it.
I am not sure why you have a self join in there...maybe you were thinking of joining to inserted?
Even if you populated your variable correctly you aren't doing anything with it.
And you will ALWAYS print 'no insertion available'.
Now to address your original issues:
when they try to insert the second record with same referenceid ,trigger comes into action and it has to show no insertion pls referenceid should be unique
If ReferenceID needs to be unique you should use a unique constraint on the column instead of using a trigger for this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply