|
Not sure if this will help but in Oracle, you have pre-defined two variables called 'new' and 'old'. The use of them is like so
create or replace trigger transaction_create_date
before insert on transaction_table
for each row
begin
:new.create_date := sysdate;
:new.modification_date := :new.create_date;
...
Chris Meech
"what makes CP different is the people and sense of community, things people will only discover if they join up and join in." Christian Graus Nov 14, 2002.
"Microsoft hasn't ever enforced its patents. Apparently they keep them for defensive reasons only. Or, they could be waiting 'til they have a critical mass of patents, enforce them all at once and win the game of Risk that they're playing with the world." Chris Sells Feb 18, 2003.
|
|
|
|
|
That is a cool feature of oracle! Thanks for the tip!
I did finally get it working by doing the following.
I can now check to see if any of the records have that number before it is inserted.
(ie)
CREATE TRIGGER My1stTrigger ON MyTableA
INSTEAD OF INSERT
AS
IF (SELECT COUNT(*) FROM MyTableA WHERE MyTableA.Number1 = (SELECT Number1 FROM inserted)) = 0
BEGIN
INSERT INTO MyTableA SELECT Id, Number1 FROM inserted
END
ELSE
[... Do something ...]
|
|
|
|
|
Dear friends,
I want to execute a query in SQL - Server but i want the result in variable. For this purpose i am successfully doing something like this:
DECLARE @num int<br />
SELECT @num = (select salary from mytable where id=2)<br />
PRINT @num
In above query i only select one field from the table i.e salary. Now can anyone tell me that how can i collect values of multiple fields in variables. e.g
(select name, salary from mytable where id=2)
In this case i am selecting two fields from a table i.e name, salary. Now how can i get these two values in variables. Any suggestion is welcomed.
Thanks
|
|
|
|
|
Try:
DECLARE @num int, @name nvarchar(50) -- Or whatever size you need
SELECT
@name = name,
@num = salary
FROM
mytable
WHERE
id = 2
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
|
|
|
|
|
Hello All,
Is there a way by SQL statement, if any, to rename a table in a database?
Or there any vendor specific way of doing it?
Best regards,
Paul.
Jesus Christ is LOVE! Please tell somebody.
|
|
|
|
|
Try a
CREATE TABLE NEWTABLE AS (SELECT * FROM OLDTABLE)
DROP TABLE OLDTABLE
there is no simple way of renaming a table
salut,
dirk
|
|
|
|
|
Thanks so much for the tip.
DiWa wrote:
there is no simple way of renaming a table
Anything simplier than what you have shown me?
Best regards,
Paul.
Jesus Christ is LOVE! Please tell somebody.
|
|
|
|
|
Hello Dirk,
Back again. I have just tried the statement using OLE DB (.NET) for MS Access. It is giving an error that the CREATE statment has an error. I separated the CREATE and the DROP. Is there anything missing?
Best regards,
Paul.
Jesus Christ is LOVE! Please tell somebody.
|
|
|
|
|
Yes, the SQL-compatibility in ACCESS is missing. The 'CREATE TABLE AS ...' statement is not supported by the JET-Engine. Sorry, you asked for a SQL-Statement, don't know how to do real SQL in Access - hate the approach MS has to standards like SQL
The statements will work on Oracle and DB2 for sure
For Access there I don't know a way to rename a table programmatically.
best regards,
dirk
|
|
|
|
|
Thanks for the explanation. I just picked a similar statement from sql.org to copy just the table structure but it also failed.
CREATE TABLE test2 () INHERITS (test)
May be I will have to disable this option for Access.
Thanks so much for the support.
Best regards,
Paul.
Jesus Christ is LOVE! Please tell somebody.
|
|
|
|
|
Paul Selormey wrote:
any vendor specific way
For SQL Server, you can use the sp_rename stored proc.
-- Rename the customers table to custs.
EXEC sp_rename 'customers', 'custs'
Without nipples, breasts would be pointless.
|
|
|
|
|
Wow, that is cool. Thanks.
Best regards,
Paul.
Jesus Christ is LOVE! Please tell somebody.
|
|
|
|
|
Dear CPians,
I am developing an ASP.NET app which has a lot of database work. The app has to support both SQL Server and Access and one of the requirements is be able to switch from SQL Server to Access by setting a parameter in a XML file(the XML file is read when the app starts).
I need to use the SQLClient classes. Will I be able to use the Access DB as well through the SQLClient classes?
If yes are there any drawbacks?
If no, how can I make my app capable of accessing both DBs?
What if in the future I need to support Oracle or/and DB2 as well?
Any help anyone?
Thank you!
|---------------|
| theJazzyBrain |
|---------------|
|
|
|
|
|
The best way to handle this kind of issue in the ADO.NET is to work with the interfaces, not the classes directly.
Provide a common point to create your connection object and return, IDbConnection interface. You can you this to access the data in a provider-independent way.
Best regards,
Paul.
Jesus Christ is LOVE! Please tell somebody.
|
|
|
|
|
And you do I work with the interfaces? Any sample code?
If I do that, will I still have the advantages that SQLClient provides when using SQL Server. Because, the app will initially be aimed for SQL server 2000, but we are planning for a Light version which works with Access in order to cut down costs for customers.
Do you think we should try and make a component to handle all the provider-independed access?
Thanx!
Jason
|---------------|
| theJazzyBrain |
|---------------|
|
|
|
|
|
theJazzyBrain wrote:
And you do I work with the interfaces? Any sample code?
IDbConnection connection = new OleDbConnection(str);
IDbConnection connection = new SqlConnection(str);
Should work for you. You can later do something like
IDbCommand atrCommand = connection.CreateCommand();
theJazzyBrain wrote:
If I do that, will I still have the advantages that SQLClient provides when using SQL Server. Because, the app will initially be aimed for SQL server 2000, but we are planning for a Light version which works with Access in order to cut down costs for customers.
If you wish to do SQL Server or for that matter any provider specific stuff, store the connection type somehow so that you can cast the pointer to the appropriate class later.
private IDbConnection conn = null;
private Type connType = null;
SqlConnection connection = new SqlConnection(str);
conn = (IDbConnection)connection;
connType = connection.GetType();
then later, you can do something like
if (connType == typeof(SqlConnection))
SqlConnection sqlConn = (SqlConnection)conn;
else if (...)
{}
theJazzyBrain wrote:
Do you think we should try and make a component to handle all the provider-independed access?
It depends on your needs. The central point here is the connection object, so provide a means of obtaining the interface where you wish to work in provider-independent manner.
Best regards,
Paul.
Jesus Christ is LOVE! Please tell somebody.
|
|
|
|
|
I understand now!
This is very helpfull!
Thank you very much Paul!
Jason
|---------------|
| theJazzyBrain |
|---------------|
|
|
|
|
|
Hello all. I have a stored procedure that updates a table based on it's own contents. The table contains web site hits and I have a C# function that later on resolves the IP addresses in the table to domain names and updates them in the table.
However before that step, the stored procedure in question sees if there are already matching IP addresses from past hits that had their domains resolved and then updates them accordingly to save time on the nslookup part of it.
Problem is that the stored procedure is getting very slow when all that has changed is the number of rows in the table has increased quite a bit, however the daily number of null domains hasn't really changed at all.
I'm wondering if anyone sees anything inherently wacky or wrong with this stored procedure:
ALTER PROCEDURE dbo.zspDomainSelfUpdate<br />
AS<br />
UPDATE dbo.hits SET dbo.hits.domain = hits_1.domain<br />
FROM dbo.hits INNER JOIN<br />
dbo.hits hits_1 ON dbo.hits.ip = hits_1.ip<br />
WHERE (dbo.hits.domain IS NULL) AND (hits_1.domain IS NOT NULL)
(there is an index on the ip field)
|
|
|
|
|
Hi J,
the problem I see is the WHERE-condition, you will (almost certain) get 2 full-table scans in resolving that. I would try this:
UPDATE dbo.hits SET dbo.hits.domain =
(SELECT dbo.hits.domain FROM dbo.hits hits1
WHERE dbo.hits.ip = hits1.ip
AND dbo.domain IS NOT NULL)
WHERE dbo.hits.domain IS NULL
I guess this leads to a somewhat shorter access-plan.
good luck,
dirk
|
|
|
|
|
Thank you! I'll give that a shot, it makes sense.
|
|
|
|
|
I was reading this article:
http://www.w3schools.com/sql/sql_join.asp[^]
And it shows the following for regular joins:
<br />
SELECT Employees.Name, Orders.Product<br />
FROM Employees, Orders<br />
WHERE Employees.Employee_ID=Orders.Employee_ID<br />
and then the following for INNER JOINS:
<br />
SELECT Employees.Name, Orders.Product<br />
FROM Employees<br />
INNER JOIN Orders<br />
ON Employees.Employee_ID=Orders.Employee_ID<br />
They seem to always return the same results. Is there a difference between the two types of queries above? Or are they the same? Is there any performance reasons or any other reasons when I should favor one type of query above the other?
Thanks!
|
|
|
|
|
I've wondered myself and the best info I could find implies that using an explicit JOIN rather than an implied JOIN helps out the optimization of the query for some types of database servers.
Personally, I think it's just a good habit to stick to explicit JOINS.
|
|
|
|
|
They will both produce the same result, and in most cases they will both optimize to the same execution plan.
Try them Query Analyzer (SQL Server) sometime, and choose to show the execution plan.
Without nipples, breasts would be pointless.
|
|
|
|
|
From SQL Books Online:
Specifying Joins in FROM or WHERE Clauses
The rows selected by a query are filtered first by the FROM clause join conditions, then the WHERE clause search conditions, and then the HAVING clause search conditions. Inner joins can be specified in either the FROM or WHERE clause without affecting the final result.
Outer join conditions, however, may interact differently with the WHERE clause search conditions, depending on whether the join conditions are in the FROM or WHERE clause. Therefore, the ability to specify Transact-SQL outer joins in the WHERE clause is not recommended, is no longer documented, and will be dropped in a future release.
For example, these queries both specify a left outer join to SELECT 23 rows that display the title identification number, title name, and the number of books sold:
-- Join in WHERE clause.
USE pubs
SELECT t.title_id, t.title, s.qty
FROM titles AS t, sales AS s
WHERE t.title_id *= s.title_id
-- Join in FROM clause.
USE pubs
SELECT t.title_id, t.title, s.qty
FROM titles AS t LEFT OUTER JOIN sales AS s
ON t.title_id = s.title_id
In this query, a search condition is also specified in the WHERE clause:
-- Join and search condition in WHERE clause.
USE pubs
SELECT t.title_id, t.title, s.qty
FROM titles AS t, sales AS s
WHERE t.title_id *= s.title_id
AND s.stor_id = '7066'
The condition stor_id = '7066' is evaluated along with the join. The join only selects the rows for stor_id 7066 from the sales table, but because it is an outer join null values are supplied as the store information in all the other rows. This query returns 18 rows.
The join condition can be moved to the FROM clause, and the stor_id condition left in the WHERE clause:
USE pubs
SELECT t.title_id, t.title, s.qty
FROM titles AS t LEFT OUTER JOIN sales AS s
ON t.title_id = s.title_id
WHERE s.stor_id = '7066'
This query returns only two rows because the restriction of stor_id = '7066' is applied after the left outer join has been performed. This eliminates all the rows from the outer join that have NULL for their stor_id. To return the same information with the join condition in the FROM clause, specify the stor_id = '7066' condition as part of the ON join_criteria section in the FROM clause and remove the WHERE clause:
USE pubs
SELECT t.title_id, t.title, s.qty
FROM titles AS t LEFT OUTER JOIN sales AS s
ON t.title_id = s.title_id
AND s.stor_id = '7066'
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
|
|
|
|
|
Does anybody know how to start/stop MSDE from code (c#) in a machine running Windows 98/ME?
In NT, I use System.ServiceProcess.ServiceController .
With ME, I've tried with SQLDMO.SQLServer2Class (which I'm also using to attach a DB, without problems), using the Start method, but it doesn't seem to work.
Thanks!
|
|
|
|