|
my question is that:
i have EMP table and also EMP_TRACK table.....suppose if im going to update EMP table the no.of rows gets effected should be stored in EMP_TRACK table.
that means EMP table simply contains columns EMPNO,ENAME.
whereas EMP_TRACK table contains columns DMLTYPE and NO_OF_ROWS_EFFECTED.
|
|
|
|
|
One way is to build a trigger on EMP and select count from inserted or deleted virtual tables.
Another way could be that you get the number of rows affected at client side and then insert it to emp_track.
One link that could be useful: Using the inserted and deleted Tables[^]
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
can't we use lik this:
create or replace trigger <name> after update on emp as
begin
if (sql%found) then
insert into emp_track values('update',sql%ROWCOUNT);
end;
But the problem is that SQL%ROWCOUNT is not returning any value........
From ur answer im able to understand that there r virtual inbuilt tables called inserted or deleted.....
so can we use directly SELECT COUNT(*) FROM INSERTED;
if so then if somebody is updating for the second time r the effected rows going to replace the existing rows of INSERTED TABLE. Hope u understood my problem....
Thanks for the answer..byee
|
|
|
|
|
Seems that you're using Oracle, correct?
If so, there's no virtual tables (inserted and deleted) in Oracle. Instead, you can use a trigger like you wrote.
The reason why sql%found isn't returning a value is that you are not executing any sql inside the trigger (sql&found is used for the last executed statement inside a pl/sql block).
Also the trigger is executed only once per statement. One way to handle this is that you modify the trigger to fire every time row is updated. So basically your trigger could look like this:
create or replace trigger emp_dml_counter
after update on emp
for each row
as
begin
insert into emp_track values('update',1);
end;
If you want to combine different statement types (insert, update and delete) to the same trigger, you can use :NEW and :OLD variables to check if the row is new, updated or deleted.
This is now Oracle specific so these suggestions won't work in SQL Server. You we're using Oracle?
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
im using sqlplus ....can i get the result if i use ur code????
and one more thing in the line....
insert into emp_track values('update',1);
emans that only 1 row got effected so value 1 is inserted wat is the case if more than 1 row is updated....
i mean that how can the data in EMP_TRACK be ('UPDATE',5) ??
|
|
|
|
|
When you specify FOR EACH ROW in a trigger it means that the trigger is fired for every row that is modified. For example if a single statement modifies 5 rows then this trigger is executed 5 times. That's why I used number 1 in the statement.
Have a try with the trigger I modified. It may contain typos, but you'll get the idea when you create the trigger and then update the emp-table.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
ya i tried the trigger ur given lik
create trigger trg_emp after update on emp
for each row
begin
insert into emp_track values('update',1);
end;
the problem im facing is that suppose if i update 5 rows in EMP table
then the command line -----INSERT INTO EMP_TRACK VALUES('UPDATE',1)----- is going to fire for 5 times thereby inserting (UPDATE,1) for 5 times.
can u suggest some method for inserting ('update',5) at once
because i hav already tried all possible solutions still not gettin gthe right solution....
Thanks in advance
|
|
|
|
|
That's exactly the behaviour I explained. I don't recall that there is any (easy) way to find out how many row are affected by a single statement from inside a trigger.
If you're just cumulating count on different DML types, the easiest way is to add one row to emp_track table before you start using the trigger. For example:
INSERT INTO Emp_Track (DmlType, DmlCount) VALUES ('UPDATE', 0);
INSERT INTO Emp_Track (DmlType, DmlCount) VALUES ('INSERT', 0);
...
This will give you the starting point for logging. After that you don't use INSERT in the trigger, but UPDATE instead. Like:
CREATE TRIGGER trg_Emp
AFTER UPDATE ON Emp
FOR EACH ROW
BEGIN
UPDATE Emp_Track
SET DmlCount = DmlCount + 1
WHERE DmlType = 'UPDATE';
END;
This will start cumulating update counts on Emp_Track (as long as the seed row exists).
The basic question is, what data you want to get while tracking DML. If you need for example date information, when the modification was done, you would make a slightly different trigger where you test if the record for today already exists or not (for example try to update it) and so on. The trigger would be something like:
CREATE TRIGGER trg_Emp
AFTER UPDATE ON Emp
FOR EACH ROW
BEGIN
UPDATE Emp_Track
SET DmlCount = DmlCount + 1
WHERE DmlType = 'UPDATE'
AND DmlTime = SYSDATE;
--
IF SQL%ROWCOUNT = 0 THEN
INSERT INTO Emp_Track (DmlType, DmlCount, DmlTime)
VALUES ('UPDATE', 1, SYSDATE);
END IF;
END;
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi all,
I am trying to select values from a table to display them next to each other instead of underneath each other.
Here is my attempt:
SELECT A.Owner, A.Amount, B.Amount<br />
FROM Payment_History A<br />
JOIN Payment_History B<br />
ON A.Owner = B.Owner<br />
AND A.Number <> B.Number<br />
WHERE A.Owner = 49622
I have been google-ing it for a while now but so far this is my best attempt.
The problem however is that the return twice (there are only two record for this member but some might have up to 15) like this:
<br />
Owner Amount Amount<br />
49622 11585.678 23143.57<br />
49622 23143.57 11585.678
Please help my get the values to look like this
<br />
Owner Amount Amount<br />
49622 11585.678 23143.57
Only once like this...
Thank you in advance...
"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison
|
|
|
|
|
Since in your query you have no joining between the tables, you'll get cartesian product.
If I understood you correctly, you want 15 columns, but a single row if owner has 15 rows. If this is correct, have a look at PIVOT[^] queries.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
thank you... i will look into it.
"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison
|
|
|
|
|
Hi everyone,
I am creating a table to represent sporting results and so far have the following columns:
---------------------------------------------------------------
game_ID date home_ID away_ID home_score away_score
---------------------------------------------------------------
I have added an integrity check to ensure that home != away, as well as a 'unique' statement with (date, home) and (date, away), however this will still allow the same team to play two games on the same date; one home and one away.
Is there a way to build a table so that no team can play two games on the same date or do I need to check this myself whenever I insert a new result?
|
|
|
|
|
JenovaProject wrote: Is there a way to build a table so that no team can play two games on the same date
Add either unique constraint or unique index on those columns (single constraint with two fields). Just make sure that the time portion in the date is the same.
Something like:
ALTER TABLE XYZ ADD CONSTRAINT UK_SingleGame UNIQUE (date, home_id);
Then if you want you can create a similar constraint for date and away_id.
The need to optimize rises from a bad design.
My articles[ ^]
modified on Saturday, November 8, 2008 2:58 AM
|
|
|
|
|
Hi ALL,
I have three table named Members (ID ,Name,Age),Interest(IntID,IntFrom,ID-refers Members)I want to create a Query Which fetches all the Interests expressed by a particular Member.
can anybody help me
Thanks A Lot
|
|
|
|
|
Three tables?
select
[name],
intFrom
from
members m
join interest i
on m.id = i.id
|
|
|
|
|
Select members.*,Interest.*<br />
from members,Interest<br />
where members.id=Interest.id
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
hi Guys , i need your Help again. i have the Following query
SELECT IDENTITY(int, 1,1) AS REC_NO,
LIS_KEY,
FUNC_KEY,
UNIT_NO,
RIGHTS_ZONING,
USE_CODE,
[OWNER],
RATEABILITY,
EXCLUSION,
MARKET_VALUE,
EFFECTIVE_DATE,
CATEGORY_CODE,
SUPPLE_NUM,
AREA,
PROPERTY_ID,
OLD_MARKET_VALUE,
VALUATION_ID,
NOTIFICATION_ID,
REASON,
SORT_DATE
INTO SDE.EXPORT_OITPS_GV_SUPP_RECON
FROM
(
SELECT DISTINCT TOP 100 PERCENT P.LIS_KEY,
CASE LEN(FUNC_KEY)
WHEN 8 THEN SUBSTRING(FUNC_KEY, 1, 5) + '0' + SUBSTRING(FUNC_KEY, 6, 3)
ELSE FUNC_KEY
END AS FUNC_KEY,
CASE LEN(FUNC_KEY)
WHEN 8 THEN SUBSTRING(FUNC_KEY,5,1)+ '0' + SUBSTRING(FUNC_KEY, 6, 4)
ELSE SUBSTRING(FUNC_KEY,5, 5)
End AS UNIT_NO,
SUBSTRING(NEW_ATTRIB_CODE, 1, 2) AS RIGHTS_ZONING,
SUBSTRING(NEW_ATTRIB_CODE, 3, 2) AS USE_CODE,
ISNULL(P.OWN_NAME, '') AS OWNER,
CASE SUBSTRING(NEW_ATTRIB_CODE, 7, 1)
WHEN '1' THEN 'R'
WHEN '4' THEN 'E'
ELSE 'N'
END AS RATEABILITY,
CASE
WHEN P.PROP_CATEGORY_ID = '2' THEN 'RES'
WHEN P.PROP_CATEGORY_ID = '4' THEN 'PSI'
WHEN SUBSTRING(V.NEW_ATTRIB_CODE, 7, 2) = '41' OR SUBSTRING(V.NEW_ATTRIB_CODE, 7, 2) = '42' THEN 'REL'
ELSE ' '
END AS EXCLUSION,
CONVERT(FLOAT,V.NEW_IMPROVED_VALUE) AS [MARKET_VALUE],
(
CASE WHEN CONVERT(VARCHAR,DATEPART(DD, EFFECTIVE_DATE)) < 10
THEN '0' + CONVERT(VARCHAR,DATEPART(DD, EFFECTIVE_DATE))
ELSE CONVERT(VARCHAR,DATEPART(DD, EFFECTIVE_DATE))
END +
CASE WHEN CONVERT(VARCHAR,DATEPART(MM, EFFECTIVE_DATE)) < 10
THEN '0' + CONVERT(VARCHAR,DATEPART(MM, EFFECTIVE_DATE))
ELSE CONVERT(VARCHAR,DATEPART(MM, EFFECTIVE_DATE))
END +
CONVERT(VARCHAR, DATEPART(YYYY, EFFECTIVE_DATE))
) AS EFFECTIVE_DATE,
SUBSTRING(V.NEW_ATTRIB_CODE,7,2) AS CATEGORY_CODE,
'S' + CONVERT(VARCHAR, SUPPL_YEAR) + '/' + CONVERT(VARCHAR, SUPPL_NO) AS SUPPLE_NUM,
CONVERT(INT, ISNULL(P.ACTUAL_EXTENT, 0)) AS AREA,
P.PROPERTY_ID,
CONVERT(INT, ISNULL(P.IMPROVED_VALUE, -1)) AS OLD_MARKET_VALUE,
V.VALUATION_ID,
0 AS NOTIFICATION_ID,
LU_V.VAL_REASON AS REASON,
V.STATUS_DATE AS SORT_DATE
FROM SDE.PROPERTY_SUMMARY P
LEFT JOIN SDE.AUTHORITY A
ON A.AUTHORITY_ID = P.AUTHORITY_ID
INNER JOIN sde.VALUATION V
ON P.PROPERTY_ID = V.PROPERTY_ID
LEFT JOIN (SELECT GISCODE, MIN(OWN_NAME) AS OWN_NAME
FROM SDE.VW_PROPERTY_DEED
GROUP BY GISCODE) D
ON P.LIS_KEY = D.GISCODE
LEFT JOIN SDE.LU_VAL_REASON LU_V
ON V.VAL_REASON_ID = LU_V.VAL_REASON_ID
WHERE
V.ARCHIVE_DATE IS NULL
AND V.ARCHIVE_DATE IS NULL
AND V.NEW_ATTRIB_CODE IS NOT NULL
AND V.EFFECTIVE_DATE = '2008/01/07'
AND V.NEW_ATTRIB_CODE <> '0009000900'
ORDER BY SORT_DATE
) TMP
and i get the Following Error
Msg 232, Level 16, State 3, Line 6<br />
Arithmetic overflow error for type int, value = 3886000000.000000.<br />
The statement has been terminated.
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
|
|
|
|
|
It seems that it may be choking on either of the following lines:
CONVERT(INT, ISNULL(P.ACTUAL_EXTENT, 0)) AS AREA,
CONVERT(INT, ISNULL(P.IMPROVED_VALUE, -1)) AS OLD_MARKET_VALUE,
I don't know anything about the data, but I'd pull this into another query and test it out to verify if it is indeed the cause of the problem:
SELECT CONVERT(INT, ISNULL(P.IMPROVED_VALUE, -1)) AS OLD_MARKET_VALUE
FROM SDE.PROPERTY_SUMMARY P
For sure, the value of '3,886,000,000' (commas added) is too large for an int; int's maximum value (32-bit integer) is 2,147,483,684. You can instead convert it to bigint (64-bit integer) and that may work.
Keep It Simple Stupid! (KISS)
|
|
|
|
|
Thanks man
This line was Causing a Problem, i have Converted it into Bigint and fine now
CONVERT(BIGINT, ISNULL(P.IMPROVED_VALUE, -1)) AS OLD_MARKET_VALUE,
Thanks
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
|
|
|
|
|
Please help im getting an error when i try to connect to sql2005 express using vb2005 express and the error is "operating system error 32(the process cannot open this file because it is being used by another process).But no process is open!!
Please help!
|
|
|
|
|
Lets see the code and Stop Posting this in all Forums
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
|
|
|
|
|
Hello friends,
I am using SQL Server 2005 and want to restrict windows authenticated users from accessing my database. Only SQL Server authenticated users should be allowed to access database. How is it possible?
Thanks..
Amit
|
|
|
|
|
Set the sql server authentication to sqlserver rather than windows.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
You have two choices:
1. create a group (or groups) in Windows (either in domain or locally) and use that group (or groups) for login.
2. use SQL Server authentication.
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
When exporting a data file from MS Access into a CSV file, the dollar amount loses the second decimal place. When clicking the mouse on the cell with the data, it shows .72 in the top address bar, but the cell itself only shows .7. What is the reason, and how do I correct it so that it shows the correct amount.
I suspect that there may need to be some formatting before it is exported, but I do not know how to format it correctly. I believe it should be formatted as "Format Cells" and "Number" with "2" decimal places, but how do I do this programmatically?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
