|
Hi,
Can someone please help me with an issue I am having?
I have created a Table named "Countries" The table has a CountryID and a Country. What needs to happen is that the user must be able to add his/her country in the database. The problem I am having is the following:
The CountryID is NOT auto numbered. I need to write a trigger that executes BEFORE the insert procedure. It basically needs to check what the latest record ID is, if there are none than the CountryID should be 1 if there are records the trigger must take the last record and add 1.
I have never worked with triggers so I am in a bit of a pickle. Any help will be appreciated!!
Illegal Operation
|
|
|
|
|
Why can't you change the countryid field into an identity (autonumber) field.
Triggers work on the inserted record and therefore fire after insert. Identity field is the designed methedology, you are trying to make a trigger do the job of another function.
Alternatively you can create an insert proc that gets the number before the insert begins. This is subject to corruption in high volume tables (2 attempts at the same time - very unlikely but must be considered).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Ok, point taken. What happens when you use auto numbering and you have 10 entries in your table, now you need to delete entry number 7. What are you going to do to fix the table??
Illegal Operation
|
|
|
|
|
Define "fix" the table. Guessing you are stressing about the missing sequential number 7 from the identity field.
This is Database 101. ID fields must NEVER have intelligent data, they are used to identify the record and link it to other tables. The sooner you get this idea the happier you will be. If you are using the ID field other than to locate the record (eg sorting) then your design is tragically flawed.
I recommend you get a book on database design and data structuring (mine are so old they are no longer published).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hold your horses! 
Triggers only work after not before, just use id_country as primarykey and set autonumber (counter) there.
I also think dont need triggers for now.
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Hi,
I have a data file which consists of data as below,
Header
xx|yy|zz
aa|bb|cc
Footer
While iam inserting into table using bulk insert, this pipe(|) is also getting inserted into the table,
here is my query iam using to insert the data using bulk insert.
BULK Insert #TmpStList
FROM 'D:\PSC07\HRDATA\HR2SSTS_TRAINING.TXT' WITH
(FirstRow=1,LastRow= 3,DATAFILETYPE='char',FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n',keepnulls)
I want result like this
fields1 2 3
xx yy zz
aa bb cc
but i got this
fileds1 2 3
Headerxx yy zz
aa bb cc
I don't want to insert (|) into Header .
|
|
|
|
|
Your header row should have as many columns as data rows. So you should modify your data to:
Header||
xx|yy|zz
aa|bb|cc
Footer||
Although you can start from second row using FirstRow=2, note that:
The FIRSTROW attribute is not intended to skip column headers. Skipping headers is not supported by the BULK INSERT statement. When skipping rows, the SQL Server Database Engine looks only at the field terminators, and does not validate the data in the fields of skipped rows.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi,
i am finishing a application that as a database, the application is a stand alone, so no network here (1 database for 1 application).
Will i get errors when installing the application in other computer with no sql server installed ?
I think i have to type ...attached...path...whatever in connection_string and all will be fine.
You can edit and get reports in the application, i only allow users to trigger store_procedures (with parameters).
Will i get errors???
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Yes. You are using a service-based database. This means that the database must be attached to a server when you use it. Sql Server must be installed on the target machine and the database attached to it (or you can attach it when using it).
Eslam Afifi
|
|
|
|
|
You can, I think, use SQL Express and include it with your distribution if it is not already installed.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Good Morning All
i have an SP and when i run the Query it runs for 1:17 Seconds and after that it throws an Error that says
Msg 8649, Level 17, State 1, Procedure PROPERTY_SUMMARY_RECREATE, Line 72
The query has been canceled because the estimated cost of this query (984356) exceeds the configured threshold of 100000. Contact the system administrator.
I went to the Properties of the Server in Management Studio as i have the following Settings
Cursor Threshold = 0
Scan Startup Procs = False
Cost Threshold For Parallelism = 1
Locks = 0
Max Degree of Parallelism = 0
Query Wait = -1
What is Wrong ?
Thank you
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Using sp_configure check option query governor cost limit. Most likely the value is 100000 meaning that if the cost is over that, query won't run.
The need to optimize rises from a bad design
|
|
|
|
|
thank you for your reply,
Can you give me an Exact Query to Run to do this ?
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
It may be that server properties window also shows this option in connection page (Use query governor...). But if it doesn't, you can do it in T-SQL like this:
EXEC sp_configure 'show advanced options', 1 -- enable advanced options
RECONFIGURE -- apply the new setting
sp_configure -- list all configuration options and their values
If you want to change the running value:
EXEC sp_configure 'query governor cost limit', 1000000 -- either modify the value
EXEC sp_configure 'query governor cost limit', 0 -- or disable query governor
RECONFIGURE -- apply the new setting
However, your original cost is really high (984356). It means that the estimated running time for your operation is about 11 days. So I believe that there must be some problem in your procedure PROPERTY_SUMMARY_RECREATE (starting from line 72).
The need to optimize rises from a bad design
|
|
|
|
|
Good Aftenoon
By Disabling the query governor helped a lot, now the SP is not giving any Errors, you definately right that the SP PROPERTY_SUMMARY_RECREATE needs to be optimized.
Thank you 
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
No problem
The need to optimize rises from a bad design
|
|
|
|
|
currently I have a table need some data populated into it, one attribute requires to show SYSDATE, can any one tell me how to insert a SYSDATE value into the table?
or can I set the value of this attribute the time I create the table??
many thanks....
|
|
|
|
|
SYSDATE? What database technology are you talking about?
|
|
|
|
|
|
You can use SYSDATE as a value so in INSERT statement you can say:
INSERT INTO TableName (Column1, Column2,...) VALUES (Value1, SYSDATE,...)
If you want to make SYSDATE a default value for a column, you can use DEFAULT constraint for the column.
Also you can set the default value in trigger using PL/SQL.
The need to optimize rises from a bad design
|
|
|
|
|
I need to be able to pull information from two tables. The first table is the master table that contains the essential information about some object, wherease the second table has some other data related to that object. I need to pull information from the first table regardless of whether there is a match in the second table (class LEFT JOIN), but if there is a record in the second table, I need to pull data only from the latest record in the second table. So, the query would look something like:
SELECT Table1.*, Table2.* FROM Table1 LEFT JOIN Table2 ON table1.field1=table2.field1 WHERE ????
I have an autonumber field in the 2nd table that can be used to retrieve the latest record from table2. Could that be used to retrieve the latest record, and, if so, how?
Thanks.
|
|
|
|
|
Your concept is correct, but a better table representation would help. What is the data in table2? What fields do you need if present and what is the autonumber field?
Tim
|
|
|
|
|
Thanks. Let's see.
From table2 at present I only need to retrieve one field of table, so I don't actually need table2.* in the select statement. Rather, I would just need something like table2.VE. The data in table 2 is simply the results of some process run on the object described in table1. They are linked by some unique object identifier. I need to pull information about the object regardless of whether the process has been run or not. If the process has been run, I need to retrieve the VE field for that object. The basic problem is that there may be multiple records for the proces run on the object, so multiple records in table2 for the object it is linked to in table1. Some of those records may be from the same day, while others may be from 3 months, 6 months, or 10 years ago. I need the latest when pulling up the data.
The autonumber field in table2 is called ID and the name of the field in table2 that I need to retrieve now is called VE (but I may need to retrieve other fields later).
I appreciate the help.
|
|
|
|
|
select t1.*, t2.ve
from table1 t1 left outer join
(select id, VE, max(recorddate) from table2
group by id, VE) t2
on t1.id = t2.id
Does that help?
Tim
|
|
|
|
|
Thanks, Tim. The query, modified to best match my situation, seemed to work as it should for the most part, though the result set still had more records than the number of records in table1. Table1 has 10080 records, whereas the result set from the query had 10087. I'll have to sort through it and find out where those additional records are coming from, but this helped me to get started.
Thanks again.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
