473,388 Members | 991 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,388 software developers and data experts.

Executing a Views from a Trigger

Hi,

I am new to using SQL. I want to be able to exucute a query that I
place in a view. I want this views to be executed every time a value
change in one of the table in a particular field. So my guess was to
use a trigger that will call the views every time the data change in
the selected table. Is this the proper way of doing thing? Should I
use other SQL tools to achive this. I search for exemple of trigger
executing views but did not found anything as of yet. Let's use this
dummy name for the exemple:

Database: DB1
Table: Tbl1
Special field in Tbl1: flag
Views name: views_01

Thank you.

Philippe

Mar 1 '06 #1
7 1813
Forgot to mention, I am using SQL 2000.

Thank you.

Mar 1 '06 #2
Hi,
I am new to using SQL. I want to be able to exucute a query that I
place in a view.
You don't execute queries inside views. You just get to use the
view as if it were a table.

Why would you want to "execute the query"?
--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
I want this views to be executed every time a value
change in one of the table in a particular field. So my guess was to
use a trigger that will call the views every time the data change in
the selected table. Is this the proper way of doing thing? Should I
use other SQL tools to achive this. I search for exemple of trigger
executing views but did not found anything as of yet. Let's use this
dummy name for the exemple:

Database: DB1
Table: Tbl1
Special field in Tbl1: flag
Views name: views_01

Mar 1 '06 #3
Hi

What exactly do you mean by "call the views every time the data change in
the selected table."?

When the data is added/updated/deleted from the underlying table the View is
automatically updated. (It is just a select statement).

Are you concerned about updating the user interface where the View is used
as a source for a datgrid or something. If so you have to find some way to
tell the Client program to requery the database, perhaps using the View.
when a database update occurs.

--
-Dick Christoph
<so*******@gmail.com> wrote in message
news:11*********************@e56g2000cwe.googlegro ups.com...
Hi,

I am new to using SQL. I want to be able to exucute a query that I
place in a view. I want this views to be executed every time a value
change in one of the table in a particular field. So my guess was to
use a trigger that will call the views every time the data change in
the selected table. Is this the proper way of doing thing? Should I
use other SQL tools to achive this. I search for exemple of trigger
executing views but did not found anything as of yet. Let's use this
dummy name for the exemple:

Database: DB1
Table: Tbl1
Special field in Tbl1: flag
Views name: views_01

Thank you.

Philippe

Mar 1 '06 #4
I am working with an application that use SQL to store it's data. What
I am trying to achieve is to execute the query below every hour.

UPDATE Tbl1
SET Tbl1.Flag = '0'
WHERE Tbl1.Flag <> '0'

UPDATE Tbl1
SET Flag = '1'
WHERE Tbl1.Key = (SELECT Tbl2.Key
FROM Tbl2
WHERE Tbl2.Key = Tbl1.Key
AND CURRENT_TIMESTAMP >= Tbl2.Expdate GROUP
BY Tbl2.Key)

DELETE Tbl2
WHERE CURRENT_TIMESTAMP >= Tbl2.Expdate

The Query does exactly what I want it to do. Now I want to launch this
query from the application I was talking about earlyer. So I thought
that if I use a trigger that will be iniated by a script in my
application by changing a value to 1 or 0 every hour. This will then
activate the SQL trigger that will execute the query above. I strore
the query in a views. So that is why I ask how to execute a views with
a trigger.

Maybe this is not the way to go, but I hope you can understand what I
am trying to do.

Thank you again.

Philippe

Mar 1 '06 #5
Hi

If you want to execute this every hour, this would be better handled in a
scheduled job. Which can be scheduled to run a SQL Statement or stored
procedure every hour.

Although you could schedule the application to run an update query (without
a Trigger or View) every hour, what if the application isn't running all the
time? or if there are muttiple instances of it running?

One point of clarification you cannot execute and Update Query in a View.

-Dick Christoph

--
<so*******@gmail.com> wrote in message
news:11**********************@t39g2000cwt.googlegr oups.com...
I am working with an application that use SQL to store it's data. What
I am trying to achieve is to execute the query below every hour.

UPDATE Tbl1
SET Tbl1.Flag = '0'
WHERE Tbl1.Flag <> '0'

UPDATE Tbl1
SET Flag = '1'
WHERE Tbl1.Key = (SELECT Tbl2.Key
FROM Tbl2
WHERE Tbl2.Key = Tbl1.Key
AND CURRENT_TIMESTAMP >= Tbl2.Expdate GROUP
BY Tbl2.Key)

DELETE Tbl2
WHERE CURRENT_TIMESTAMP >= Tbl2.Expdate

The Query does exactly what I want it to do. Now I want to launch this
query from the application I was talking about earlyer. So I thought
that if I use a trigger that will be iniated by a script in my
application by changing a value to 1 or 0 every hour. This will then
activate the SQL trigger that will execute the query above. I strore
the query in a views. So that is why I ask how to execute a views with
a trigger.

Maybe this is not the way to go, but I hope you can understand what I
am trying to do.

Thank you again.

Philippe

Mar 1 '06 #6
How do I run a SQL statement or stored procedure every hour?

It can not run the the query all the time. I am doing other
manipulation withing my application. The 2 jobs needs to be
synchronise +- 5 min.

Thank you again for the help.

Philippe

Mar 1 '06 #7
(so*******@gmail.com) writes:
How do I run a SQL statement or stored procedure every hour?
As Dick said, schedule a job to run from SQL Server Agent.
It can not run the the query all the time. I am doing other
manipulation withing my application. The 2 jobs needs to be
synchronise +- 5 min.


In SQL Agent, a job can have several steps that executed in order. This
may be something for you.

It is difficult from your abstract definition to say whether you are
on the right track at all. If you can describe the underlying business
problem, it is more likely that you will get useful advice.
--
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
Mar 1 '06 #8

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

Similar topics

4
by: Rowan | last post by:
Hi there, it's me again. I am having trouble with a view again. I am trying to do a calculation, but there are some checks that need to be taken into consideration. Maybe a view is not the right...
0
by: Marko Poutiainen | last post by:
Situation: We had to make our SQLServer 2000 database multi-lingual. That is, certain things (such as product names) in the database should be shown in the language the user is using (Finnish,...
10
by: Heiko Pliefke | last post by:
Hi NG! I wrote a stored procedure which at first deletes 100.000 rows and then inserts 100.000 new rows. There is a huge difference between executing this SP in the query-analyzer (runtime...
2
by: Pascal Polleunus | last post by:
(another try with a different subject as it doesn't seem to work with "EXECUTE + transaction = unexpected error -8" :-/) Hi, It seems that there is a problem when executing a dynamic...
2
by: dbuchanan52 | last post by:
Hello, I am building an application for Windows Forms using. I am new to SQL Server 'Views'. Are the following correct understanding of their use? 1.) I believe a view can be referenced in a...
5
by: Karl | last post by:
Can a View be used to avoid deadlocks? I have a trigger that needs to select data from a row of a table that may have been locked (possibly by the same execution thread that caused the trigger...
11
by: JC | last post by:
I created an updateable view, which joins two tables, in a DB2 UDB database. The view was made updateable by the creation of an "instead of" trigger. I'd like to use this view for updates via QMF...
0
by: wugon.net | last post by:
Hi , Anyone know how to monitor db2 trigger activity ? We suffer some trigger issue today and we try to monitor trigger's behavior use event monitor and db2audit, but both tools can not get...
7
by: Gary | last post by:
Hello guys! Bear with me, I am a newbie. She is the Data Warehouse manager. She has about 50 users to use the Oracle database from M$ Access via ODBC connection. All those users have only...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
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...
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
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

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.