Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Check Data While Inserting Expand / Collapse
Author
Message
Posted Wednesday, March 20, 2013 1:24 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 2:17 AM
Points: 203, Visits: 499
Dear All,
I have a UI like,

LineNo ProdID Discount(%) 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.

LineNo	ProdID	Discount(%)
------ ------ -------
1 1000 0
2 1001 2
3 1002 19
4 1003 0

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
Post #1433025
Posted Wednesday, March 20, 2013 3:17 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062

...
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?


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1433075
Posted Wednesday, March 20, 2013 3:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 2:17 AM
Points: 203, Visits: 499
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

Post #1433081
Posted Wednesday, March 20, 2013 3:36 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 7, 2014 7:27 AM
Points: 23, Visits: 524
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"
Post #1433087
Posted Wednesday, March 20, 2013 4:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
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.


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1433109
Posted Thursday, March 21, 2013 1:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 2:17 AM
Points: 203, Visits: 499
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.
Post #1433616
Posted Thursday, March 21, 2013 1:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 2:17 AM
Points: 203, Visits: 499
@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.
Post #1433617
Posted Thursday, March 21, 2013 3:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
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.




_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1433670
Posted Friday, March 22, 2013 10:20 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 2:17 AM
Points: 203, Visits: 499

@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.
Post #1434566
Posted Saturday, March 23, 2013 12:33 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:41 PM
Points: 23,033, Visits: 31,555
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1434574
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse