March 20, 2013 at 1:24 am
Dear All,
I have a UI like,
LineNoProdIDDiscount(%) and a "Insert" Button
----------------------
I can insert a single or multiple lines in this UI.
While inserting the following data, it will check whether there is any data in Discount Column greater than 18. If so, it will not insert any data and raise an error message.
LineNoProdIDDiscount(%)
-------------------
110000
210012
3100219
410030
I coded but it always checks LineNo1. Please help me to code whether I can check data that is greater than 18 in Discount Column against all the LineNo .
Regards,
Akbar
March 20, 2013 at 3:17 am
...
I coded but it always checks LineNo1. Please help me to code whether I can check data that is greater than 18 in Discount Column against all the LineNo .
...
Could you please post what have you "coded" so far?
March 20, 2013 at 3:30 am
CREATE TRIGGER [dbo].[Discount]
ON [dbo].[Product]
INSTEAD OF INSERT
AS
Declare @disc int;
Declare @line int;
Declare @pdid as varchar(10);
BEGIN
select @disc = i.DiscPercentage from inserted i
select @line = i.LineNumber from inserted i
select @pdid = i.ProdId from inserted i
IF (@disc>18 and @pdid between 1000 and 2000)
RAISERROR(N'Please Type Correct Discount',16,1);
ROLLBACK;
END
March 20, 2013 at 3:36 am
You only show the trigger-code, but there is more code involved.
In your opening-post you mentioned that you can insert multiple rows at once.
How is that INSERT-part coded?
But I can tell you this from looking at the trigger-code: Your trigger will only work correctly - and as expected - when you insert only one row!
"Walking on water and developing software from a specification are easy if both are frozen." -- Edward V. Berard, "Life-Cycle Approaches"
March 20, 2013 at 4:04 am
shohelr2003 (3/20/2013)
CREATE TRIGGER [dbo].[Discount]
ON [dbo].[Product]
INSTEAD OF INSERT
AS
Declare @disc int;
Declare @line int;
Declare @pdid as varchar(10);
BEGIN
select @disc = i.DiscPercentage from inserted i
select @line = i.LineNumber from inserted i
select @pdid = i.ProdId from inserted i
IF (@disc>18 and @pdid between 1000 and 2000)
RAISERROR(N'Please Type Correct Discount',16,1);
ROLLBACK;
END
You shouldn't write trigger as above. This is very insecure way to write it.
inserted and deleted internal tables can contain multiple rows, therefore you cannot read from them into variables. Also, in your instance , you need just simple FOR INSERT trigger - not INSTEAD OF one:
CREATE TRIGGER [dbo].[tr_Product_Discount]
ON [dbo].[Product]
FOR UPDATE
AS
BEGIN
IF EXISTS (SELECT 1 FROM inserted
WHERE DiscPercentage > 18
AND ProdId between 1000 and 2000)
BEGIN
RAISERROR(N'Please Type Correct Discount',16,1);
ROLLBACK;
END
END
Saying the above, the trigger is really overkill design here.
The better design would be creating table check constraint and validation in UI.
March 21, 2013 at 1:48 am
Eugene Elutin (3/20/2013)
CREATE TRIGGER [dbo].[tr_Product_Discount]ON [dbo].[Product]
FOR UPDATE
AS
BEGIN
IF EXISTS (SELECT 1 FROM inserted
WHERE DiscPercentage > 18
AND ProdId between 1000 and 2000)
BEGIN
RAISERROR(N'Please Type Correct Discount',16,1);
ROLLBACK;
END
END
Saying the above, the trigger is really overkill design here.
The better design would be creating table check constraint and validation in UI.
Thank for your reply. It helps me. Actually for the short run, I am doing so. But the ultimate design will be developed in UI.
Thank again.
March 21, 2013 at 1:53 am
@Eugene Elutin,
Can you please tell me the basic difference between FOR INSERT and INSTEAD OF INSERT Trigger.
I know there are lots of resources on it. But please tell me in short.
March 21, 2013 at 3:45 am
shohelr2003 (3/21/2013)
@Eugene Elutin,
Can you please tell me the basic difference between FOR INSERT and INSTEAD OF INSERT Trigger.
I know there are lots of resources on it. But please tell me in short.
In short?
Number of words! FOR INSERT has two, INSTEAD OF INSERT has three! 😉
Ok, only joking...
The main difference is: FOR INSERT trigger is the one which will shot before inserted data is committed into table. INSTEAD OF trigger, will stop any insert happening and replace it with the logic you have in this trigger. Basically, your INSTEAD OF trigger can actually do delete or update or anything else with its own or any other table.
When you may want to use it?
One of the common examples would be when you want make non-updatedable view to be updateable. Using INSTEAD OF trigger will help you here. Let's say you view if join between three tables. You cannot insert directly into this view. However, you can add INSTEAD OF trigger which will use specific logic and insert records into three separate tables.
March 22, 2013 at 10:20 pm
@Eugene Elutin,
In short?
Number of words! FOR INSERT has two, INSTEAD OF INSERT has three! [Wink]
That's a good SSPrank.
You are really SSCrazy. Thank for your help.
March 23, 2013 at 12:33 am
I would look at using a check constraint instead of a trigger. Even if you implement the check in the UI, it should be in the database as well to prevent erroneous data from being inserted via other means.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply