|
hi,
at oracle db i want to backup a row when an update request come for the row. how can i make this with pl/sql?
|
|
|
|
|
use Update Trigger.
Whenever the row is updated use :old and :new objects to get the data from updated row and new row.
If it is after update trigger, you need to use :old to get old values otherwise use select from table to get the value.
Now insert them to the log table. For instance :
CREATE OR REPLACE TRIGGER orders_after_update
AFTER UPDATE
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
-- Find username of person performing UPDATE into table
SELECT user INTO v_username
FROM dual;
-- Insert record into audit table
INSERT INTO orders_audit
( order_id,
quantity_before,
quantity_after,
username )
VALUES
( :new.order_id,
:old.quantity,
:new.quantity,
v_username );
END;
|
|
|
|
|
Hi,
Would you know why parameterized queires in a MS Access 2000 DB can't be seen from VS 2008 Report Wizard or Crystal Reports 2008?
When I try to create the report using their wizards, none of them see or detect any queries that use parameters, they only pick the ones that have no parameters at all.
The queries are fully functional, and I am able to invoke them from code.
Thanks for you assistance.
Cheers!
|
|
|
|
|
Hi All,
In MYSQL
1)we migrated one project from Oracle to MYSQL.
There is one Procedure that is called by many other procedures.
We will call the procedure Level2 (Called Procedure)
The Level1 (Calling ) Procedure is calling the Level2 Procedure.
If something Fails in Procedure2 - Level2 (.. Called Procedure );
Any other statements in Level1 Procedure before calling proc level2 - should be rolled back since autocommit=0; In Oracle this works fine. Can some one tell How to implement the same in MySQL - Innodb. Or do we have to use falcon for the same.
(In MySql as soon as we call the Level2 Procedure in Level1 - In Level2 procedure there is begin and due to this begin the previous trnasactions are commited )
Details :
Table T
DROP TABLE IF EXISTS `bestr`.`t`;
CREATE TABLE `bestr`.`t` (
`c` binary(3) DEFAULT NULL,
`Col1` varchar(45) NOT NULL DEFAULT '',
`Col2` varchar(45) DEFAULT NULL,
`LCT` datetime DEFAULT NULL,
`DeleteFlag` varchar(1) DEFAULT NULL,
PRIMARY KEY (`Col1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Insert into t Values ('1', '1', '1', CURRENT_TIMESTAMP, 'N');
Insert into t Values ('1', '2', '1', CURRENT_TIMESTAMP, 'N');
Insert into t Values ('1', '3', '1', CURRENT_TIMESTAMP, 'N');
-------------------- Proc 1 ---------
DELIMITER $$
DROP PROCEDURE IF EXISTS `bestr`.`lEVEL1` $$
CREATE DEFINER=`BESTR`@`%` PROCEDURE `lEVEL1`(
INO varchar(26),
Out R2 Int,
Out RC Int
)
Begin
Declare R1 Int;
Set AutoCommit=0;
Set R1=0;
Set R2=0;
START TRANSACTION ;
Update T Set COL2 = Col2 + 1 Where COL1 = INO;
-- Block1
Call Level2 ( INO, R1, RC);
Set R2=R1;
-- Block 1 end (Note if Block 1 is commented the transaction is implemented properly.
-- Set R2= ROW_COUNT();
if R1 > 0 then
Commit;
else
RollBack;
end if;
end $$
DELIMITER ;
========================================================
DELIMITER $$
DROP PROCEDURE IF EXISTS `bestr`.`lEVEL2` $$
CREATE DEFINER=`BESTR`@`%` PROCEDURE `lEVEL2`(
INO2 varchar(26),
Out R1 Int,
Out RC int
)
Begin
Set AutoCommit=0;
Set R1=0;
START TRANSACTION;
Select Count(1) into RC from T Where COL1 = INO2;
Update T Set LCT = CURRENT_TIMESTAMP Where COL1 = INO2;
Update T Set C = 'FailTest' Where COL1 = INO2;
Select ROW_COUNT() into R1;
if R1 > 0 then
Commit ;
else
Rollback ;
end if;
end $$
DELIMITER ;
==========================================================
---------------------------------------------
Call Level1 ('3', @R1, @R2);
Select @R1, @R2;
2) Is reliable, tested falcon Download link is available?
Your inputs will be very useful.
Regards
Manoj
|
|
|
|
|
I am having to build queries on a new database through an ODBC (our company just bought new POS software) to do reports. One of the DBA's at the company showed me how to access some of the data in MS Access. I am trying to do the equivalent in SQL (We are using MS SQL 2000 and do not need an upgrade for what we use it to do).
The first query is this:
SELECT TICKET.[TICKET-STORE], TICKET.[TICKET-ACCT-MANAGER], TICKET.[TICKET-TYPE], TICKET.[TICKET-CONTRACT-DATE], TICKET.[TICKET-NEXT-DUE-DATE], TICKET.[TICKET-LAST-PAID-DATE], DateDiff('d',Now()-1,[TICKET-NEXT-DUE-DATE]) AS Expr1, TICKET.[TICKET-TICKET-NBR]
FROM TICKET
WHERE (((TICKET.[TICKET-TYPE])="O")); Which is named qryactive. Then all of the other queries select from qryactive. My question is - Is there any more efficient way in MSSQL other than building a table out of that query then selecting from it?
The second is the use of TRANSFORM in MS Access. There is not an equivalent in MSSQL 2000. I read a few articles on how to possibly do it, but I get lost.
Could someone point me in the right direction?
Jude
|
|
|
|
|
1) A more efficient way to handle the query based on the results of another query is to build a MS-SQL view. In your case I belive the view would be equal to "qryactive". The other queries would reference this view just like a table in the "from" clause.
2) I believe the equivalent to TRANSFORM would be PIVOT in SQL-Server. However, I'm not 100% sure that PIVOT is available in SQL 2000.
|
|
|
|
|
|
|
Hi,
I developed a small website using express edition and MS SQL server 2005(sql server management studio express). Now iam planning to host the application.To publish website with express edition is possible?
What is difference between Proffesional and standard editions of visual studio?
To publish a website standard edition is enough?
And now coming to SQL server whether licensed key is required?
Thanks in advance
Regards,
Prathap
|
|
|
|
|
prathapcode wrote: And now coming to SQL server whether licensed key is required?
AFAIK sql server express can be freely distributed. Just you need to create a setup application and execute its installer from your program.
|
|
|
|
|
hi,
I want to search particular string in entire database and i need to retun the full row in Mysql...
Regards
Jayabharathi Ramasubbu
|
|
|
|
|
|
Hi,
I have requirement to update the weekno and StartdayofWeek.
By using the below query I can get weekno and StartdayofWeek but I need to insert/update these values in respective columns in the original table. Can any one help me.
Select BugDate,
DATEADD(wk, DATEDIFF(wk, 6,[BugDate]), 6) as startdayofweek,
Datepart(wk,[BugDate]) + ((Datepart(year,[BugDate]) - 2008) * 52) as WeekNumber
from [BugsDB_DefectVolume]
Table:
ID BugDate Weekno BugNo StartDayofWeek
111 2009-04-12 NULL 3 NULL
111 2009-04-19 NULL 1 NULL
111 2009-04-26 NULL 1 NULL
111 2009-05-03 NULL 1 NULL
111 2009-05-10 NULL 9 NULL
111 2009-05-17 NULL 9 NULL
111 2009-05-24 NULL 4 NULL
111 2009-05-31 NULL 2 NULL
111 2009-06-07 NULL 4 NULL
Thanks.
|
|
|
|
|
hi,
if both table having unique column,then u can use below query
update MyTable
set NtextColumn = ot.NtextColumn
from dbo.MyTable
join anotherDB.dbo.OtherTable ot
on MyTable.KeyColumn = ot.KeyColumn
|
|
|
|
|
sorry there is no other table. We have only one table called "BugsDB_DefectVolume". In that single table we need to update weekno and Startdayofweek using Bugdate column...
|
|
|
|
|
hi,
create one temporarytable...
insert values to temporary table...
and use previous post query...
if i'm wrong plz excuse
|
|
|
|
|
Hi,
Update the target table based on bug date
Here is the example
Just creating a dummy source table
declare @tblSource table(bugdate datetime)
insert into @tblSource
select '2009-04-12' union all
select '2009-04-19' union all
select '2009-04-26' union all
select '2009-05-03' union all
select '2009-05-10' union all
select '2009-05-17' union all
select '2009-05-24' union all
select '2009-05-31' union all
select ' 2009-06-07'
Your target table schema
create table tblTarget (id int,bugdate date,weekno int,bugno int,startdayofweek datetime)
insert into tblTarget
select 111,'2009-04-12',null,3,null union all
select 111,'2009-04-19',null,1,null union all
select 111,'2009-04-26',null,1,null union all
select 111,'2009-05-03',null,1,null union all
select 111,'2009-05-10',null,9,null union all
select 111,'2009-05-17',null,9,null union all
select 111,'2009-05-24',null,4,null union all
select 111,'2009-05-31',null,2,null union all
select 111,' 2009-06-07',null,4,null
select * from tblTarget
Output (Before updation)
id bugdate weekno bugno startdayofweek
111 2009-04-12 NULL 3 NULL
111 2009-04-19 NULL 1 NULL
111 2009-04-26 NULL 1 NULL
111 2009-05-03 NULL 1 NULL
111 2009-05-10 NULL 9 NULL
111 2009-05-17 NULL 9 NULL
111 2009-05-24 NULL 4 NULL
111 2009-05-31 NULL 2 NULL
111 2009-06-07 NULL 4 NULL
Next execute the query
;with cte as
(
select bugdate,
DATEADD(wk, DATEDIFF(wk, 6,bugdate), 6) as startdayofweek,
Datepart(wk,bugdate) + ((Datepart(year,bugdate) - 2008) * 52) as WeekNumber
from @tblSource
)
update tblTarget
set tblTarget.weekno = c.WeekNumber,tblTarget.startdayofweek = c.startdayofweek
from cte c
where c.bugdate = tblTarget.bugdate
Output(After Updation)
id bugdate weekno bugno startdayofweek
111 2009-04-12 68 3 2009-04-12 00:00:00.000
111 2009-04-19 69 1 2009-04-19 00:00:00.000
111 2009-04-26 70 1 2009-04-26 00:00:00.000
111 2009-05-03 71 1 2009-05-03 00:00:00.000
111 2009-05-10 72 9 2009-05-10 00:00:00.000
111 2009-05-17 73 9 2009-05-17 00:00:00.000
111 2009-05-24 74 4 2009-05-24 00:00:00.000
111 2009-05-31 75 2 2009-05-31 00:00:00.000
111 2009-06-07 76 4 2009-06-07 00:00:00.000
Hope this helps.
Niladri Biswas
modified on Friday, November 6, 2009 7:26 AM
|
|
|
|
|
Hi all,
I am trying to get the data from 2 tables which are in 2 different servers , to get that i have used
"sp_addlinkedserver" .Using this I am able to connect to the server also .I have used the following commands
for example my server ip is 11.12.23.121
EXEC sp_addlinkedserver '11.12.23.121'
EXEC sp_addlinkedsrvlogin @rmtsrvname = '11.12.23.121'
after this I am not bale to get the results of the linked server using following query
select * from '11.12.23.121'.testdb.dbo.userreg
Please tell me how to get the data from the linked server.
Thanks in advance
|
|
|
|
|
Try the following
select * from [11.12.23.121].testdb.dbo.userreg
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
HI,
Thank for your reply , i have tried to get the data using the query but getting the following error
"OLE DB provider "SQLNCLI" for linked server "[server]" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "[server]" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53]. "
can you please tell me the solution for this.
Thank you
|
|
|
|
|
When setting up the linked server you have not applied the correct credentials. When, under duress, we need to do this we use a SQL standard account between servers and set it up in SSMS when creating the linked server.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have two irrelavent Microsoft Access tables. They have the same number of records but no other relations. Is there a way to create a new table using SQL with fields from the two source tables put together?
For example:
Table1:
Name Address
---------------------
John 302 ABC Street
Smith 412 DEF Street</code>
Table2:
Phone email
----------------------
123-4567 m1@hotmail.com
234-5678 s1@gmail.com
Can I use a simple SQL command to create:
Table3:
Name Address Phone email
--------------------------------------------------
John 302 ABC Street 123-4567 m1@hotmail.com
Smith 412 DEF Street 234-5678 s1@gmail.com
I was thinking of using a join operation but the two source tables does not have any relations, and I can't join the table by record numbers. The only thing that's guaranteed is that the two source tables have the same amount of records.
Is there any way to achieve this just by using SQL commands without resort to writing a custom program?
Thanks!
|
|
|
|
|
I'm wondering what you'd do if you had to 'resort to writing a custom program?' - since as you say, they are (I'll use the terms) disjoint/un-related instead of your 'irrelavent'
since you dont have anything in common between the tables, and as you say, you cant join by record numbers the only way I can see of doing this (which assumes you can see record numbers) is to manually create a 'link' table with two columns, record-number-table-1, record-number-table-2, and manually enter the pairs of corresponding record numbers into it using 'eyeball matching' and 'insert' statements - of course, if you have a truckload of data this is going to take a while. You then select record from each table based on the rows in the 'link' table
There must be more to the situation than you're showing us here - To 'write a custom program' implies you know of some relationship between the tables/data, but just because they 'have the same amount of records' is a bad assumption - what about duplicates for example ?
'g'
|
|
|
|
|
Hi,
if everything else fails, you could add a field to both tables ("ID") and fill it with sequential numbers.
Then join both tables on the new field.
Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
|
|
|
|
|
Try this
declare @tbl1 table(name varchar(20), address varchar(50))
insert into @tbl1
select 'John','302 ABC Street' union all select 'Smith','412 DEF Street'
declare @tbl2 table(phone varchar(20), email varchar(50))
insert into @tbl2
select '123-4567','m1@hotmail.com' union all select '234-5678','s1@gmail.com'
;with cte1 as (select t1.*, ROW_NUMBER()over (order by name) as rn from @tbl1 t1)
,cte2 as (select t2.*, ROW_NUMBER()over (order by phone) as rn from @tbl2 t2)
select c1.name,c1.address,c2.phone, c2.email
from cte1 c1
inner join cte2 c2
on c1.rn = c2.rn
The output is
name address phone email
John 302 ABC Street 123-4567 m1@hotmail.com
Smith 412 DEF Street 234-5678 s1@gmail.com
But ur database design is wrong.. normalize that please.
I use the Row_number() function to accomplish the task....
Niladri Biswas
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
