471,592 Members | 1,079 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,592 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 1759
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 discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Rowan | last post: by
reply views Thread by Marko Poutiainen | last post: by
5 posts views Thread by Karl | last post: by
reply views Thread by wugon.net | last post: by
reply views Thread by leo001 | last post: by

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.