473,387 Members | 1,420 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,387 software developers and data experts.

need information for "Before Select" Trigger

hi guys!
Currently i'm facing a bit odd situation. i cant modify my code so i
have to make changes in SQL Server.

the problem is, i've modified one table by adding new column and now i
want only those row which has not null value in that column whenever
the select statement gets fire. i mean in every select statement only
those rows should be returned which has NOT NULL value in the newly
added column.

for this i've created one view and tried to run the application but the
i'm facing some problem as my application is developed in .net 2.0,
i've used lots of .net functionalities and now i cant make any changes
in Code.

another solution i think is triggers. if i can fire a trigger before
any select statements, my problem can be solved instantly but i'm
afraid i can not write andy "before select" trigger.

if anyone has any idea or any other solution to my problem, please let
me know.

all suggestions are welcomed.

thanks,
Lucky

Jun 8 '06 #1
4 6817
Lucky (tu************@gmail.com) writes:
Currently i'm facing a bit odd situation. i cant modify my code so i
have to make changes in SQL Server.

the problem is, i've modified one table by adding new column and now i
want only those row which has not null value in that column whenever
the select statement gets fire. i mean in every select statement only
those rows should be returned which has NOT NULL value in the newly
added column.

for this i've created one view and tried to run the application but the
i'm facing some problem as my application is developed in .net 2.0,
i've used lots of .net functionalities and now i cant make any changes
in Code.

another solution i think is triggers. if i can fire a trigger before
any select statements, my problem can be solved instantly but i'm
afraid i can not write andy "before select" trigger.


There are no BEFORE SELECT triggers. (In fact, there are no BEFORE triggers
at all in SQL Server.)

The way to handle this would be define a view, which includs a filter on
this column.

However, personally, I would not add a view for such a simple condition,
but rather add it as needed.

If you real problem is that this table is referred to in lots of places
(lots => more than 20), and you want this column to take effect every
where, you could renamed the table, and then create a view with that
carries on the original name of the table.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 8 '06 #2
Hi Pal,
i did the same way but as i said i'm using .net 2.0's dataset which
seems doesnt understands the view. As i've added "Enforced Constraints"
in the Dataset, may be that could not allowed the View to be considered
with foriegn key, unique value, not null constraint.

to avoid this situation i need to some other way. and yes it is true.
i'm reffering it in more then 20 webpages so i cant modifiy it.

anyways thanks for help.

Lucky

Erland Sommarskog wrote:
Lucky (tu************@gmail.com) writes:
Currently i'm facing a bit odd situation. i cant modify my code so i
have to make changes in SQL Server.

the problem is, i've modified one table by adding new column and now i
want only those row which has not null value in that column whenever
the select statement gets fire. i mean in every select statement only
those rows should be returned which has NOT NULL value in the newly
added column.

for this i've created one view and tried to run the application but the
i'm facing some problem as my application is developed in .net 2.0,
i've used lots of .net functionalities and now i cant make any changes
in Code.

another solution i think is triggers. if i can fire a trigger before
any select statements, my problem can be solved instantly but i'm
afraid i can not write andy "before select" trigger.


There are no BEFORE SELECT triggers. (In fact, there are no BEFORE triggers
at all in SQL Server.)

The way to handle this would be define a view, which includs a filter on
this column.

However, personally, I would not add a view for such a simple condition,
but rather add it as needed.

If you real problem is that this table is referred to in lots of places
(lots => more than 20), and you want this column to take effect every
where, you could renamed the table, and then create a view with that
carries on the original name of the table.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx


Jun 8 '06 #3
Lucky wrote:
Hi Pal,
i did the same way but as i said i'm using .net 2.0's dataset which
seems doesnt understands the view. As i've added "Enforced Constraints"
in the Dataset, may be that could not allowed the View to be considered
with foriegn key, unique value, not null constraint.

to avoid this situation i need to some other way. and yes it is true.
i'm reffering it in more then 20 webpages so i cant modifiy it.

anyways thanks for help.

Lucky


I don't think I understand what your problem is but you can obtain the
same effect as a "before" trigger by referring to the virtual table
called Deleted within the trigger. The Deleted table contains the state
of the changed rows before the statement was executed.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

Jun 8 '06 #4
Lucky (tu************@gmail.com) writes:
i did the same way but as i said i'm using .net 2.0's dataset which
seems doesnt understands the view. As i've added "Enforced Constraints"
in the Dataset, may be that could not allowed the View to be considered
with foriegn key, unique value, not null constraint.

to avoid this situation i need to some other way. and yes it is true.
i'm reffering it in more then 20 webpages so i cant modifiy it.


Looks like you have to modify 20+ web pages one way or another.

I don't really know how your .Net looks like, but permit me to point
out that had you retrieved the data with stored procedures, the change
would have been snap.

Then again, not even that should be necessary. Had you defined the
SelectCommand for the adapter in one place, it would also have been
a simple thing.

So when you modify your 20+ web pages, you strive to find a strategy
so that it does not happen again.

You may also want to visit microsoft.public.dotnet.framework.adonet for
better advice for the ADO .Net side of things.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 8 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Phil Powell | last post by:
$sql = 'INSERT INTO fs_usermetadata (' . substr(trim($cols), 0, strrpos(trim($cols), ',')) . ') VALUES (' . substr(trim($values), 0, strrpos(trim($values), ',')) . ')'; if (!mysql_query($sql))...
23
by: ian justice | last post by:
Before i post actual code, as i need a speedyish reply. Can i first ask if anyone knows off the top of their head, if there is a likely obvious cause to the following problem. For the moment i've...
2
by: caprice | last post by:
I'm a MySQL newbie. As I have to access MySQL database in Pocket PC, I'm developing a evc++ program to read and retrieve data from MySQL table. Where can I get some detail information about how...
1
by: Andrew McNab | last post by:
Hi folks, I have a problem with an MS Access SQL query which is being used in an Access Report, and am wondering if anyone can help. Basically, my query (shown below) gets some records from a...
2
by: Robert Fitzpatrick | last post by:
Anytime I create an INSERT BEFORE trigger that includes a query on the same table as the trigger is assigned, the insert does not happen without error. I get 'INSERT 0 0'. It is like the query...
12
by: spibou | last post by:
Why is a pointer allowed to point to one position past the end of an array but not to one position before the beginning of an array ? Is there any reason why the former is more useful than the...
3
by: Michel Esber | last post by:
Hello Environment: DB2 V8 LUW FP12. I have a function that returns a table. I am trying to use it inside a before trigger: create trigger TRG.T_MACHINE_RTM before insert on...
7
by: php_mysql_beginer911 | last post by:
Hi .. hope someone will help i am trying to figure it out why i cannot post string "union select" every time i try to post data which content union and select .. the page doesn't get posted and...
1
by: jvskarthick | last post by:
Whenever the “SELECT” command execution happens irrespective of any table inside one particular database Log information into one Log table. The Information required 1) User logged in ...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.