Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Check Data While Inserting


Check Data While Inserting

Author
Message
shohelr2003
shohelr2003
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 Visits: 610
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
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3040 Visits: 5478

...
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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
shohelr2003
shohelr2003
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 Visits: 610

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   


marc.snoeys
marc.snoeys
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 849
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"
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3040 Visits: 5478
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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
shohelr2003
shohelr2003
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 Visits: 610
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.
shohelr2003
shohelr2003
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 Visits: 610
@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.
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3040 Visits: 5478
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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
shohelr2003
shohelr2003
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 Visits: 610

@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.
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24157 Visits: 37925
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.

Cool
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search