|
Thank you very much for your replies.. much appreciated..
I will be looking at this problem very soon, so check your email for this post =D
Something came up and I have to focus on that first..
I will provide the code soon.
|
|
|
|
|
Hi,
i would like to ask how we can unify two triggers!
In first trigger i want to do something when a column is updated and in the other trigger i want to do something else when a different column is updated at the same table.
Now i have the two triggers and they are working properly, but i still have to do it in the same trigger!
Does anyone know how to do it!
CREATE TRIGGER UPDATE_REST1
ON [DBO].[FINTRADE]
FOR UPDATE
AS
IF UPDATE(FTRIDPAYMENT)
UPDATE FINTRADE
SET Z_YPOLOIPO= (SELECT ISNULL(I.TOTLAMOUNT,0) - ISNULL(PLH.TOTLAMOUNT,0) AS Z_YPOLOIPO
FROM INSERTED I, FINTRADE PLH
WHERE I.FTRIDPAYMENT=PLH.ID)
FROM FINTRADE F, INSERTED I
WHERE F.ID=I.ID
CREATE TRIGGER UPDATE_REST2
ON [DBO].[FINTRADE]
FOR UPDATE
AS
IF UPDATE (TOTLAMOUNT)
UPDATE FINTRADE
SET Z_YPOLOIPO= (SELECT ISNULL(PAR.TOTLAMOUNT,0) - ISNULL(I.TOTLAMOUNT,0) AS Z_YPOLOIPO
FROM INSERTED I, FINTRADE PAR
WHERE PAR.FTRIDPAYMENT=I.ID)
FROM FINTRADE F, INSERTED I
WHERE F.FTRIDPAYMENT=I.ID
Thanks in advance!
andreas
|
|
|
|
|
CREATE TRIGGER UPDATE_REST1
ON [DBO].[FINTRADE]
FOR UPDATE
AS
IF UPDATE(FTRIDPAYMENT)
UPDATE FINTRADE
SET Z_YPOLOIPO= (SELECT ISNULL(I.TOTLAMOUNT,0) - ISNULL(PLH.TOTLAMOUNT,0) AS Z_YPOLOIPO
FROM INSERTED I, FINTRADE PLH
WHERE I.FTRIDPAYMENT=PLH.ID)
FROM FINTRADE F, INSERTED I
WHERE F.ID=I.ID
IF UPDATE(TOTLAMOUNT)
UPDATE FINTRADE
SET Z_YPOLOIPO= (SELECT ISNULL(PAR.TOTLAMOUNT,0) - ISNULL(I.TOTLAMOUNT,0) AS Z_YPOLOIPO
FROM INSERTED I, FINTRADE PAR
WHERE PAR.FTRIDPAYMENT=I.ID)
FROM FINTRADE F, INSERTED I
WHERE F.FTRIDPAYMENT=I.ID
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
I have already try this!But the application which runs triggers is treated like two triggers!In the aplication which i work i can't run more than 3 triggers and i have two more!
|
|
|
|
|
andreas_635 wrote: the application which runs triggers is treated like two triggers
I don't understand?
I assume you are using SQLServer, based on the trigger syntax. Which version?
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Greetings:
I have been using SQL Server 2000 on this lap-top from the office for a few months now, learning the basics of SQL. I'm pretty sure that when it was installed, they specified that the logon password would be the same as that which is used to logon to the lap-top itself (NT-Server Authentication?). SQL just automatically started each time I started the LT and I just took all that for granted.
Yesterday, I had to change the password for my laptop as we are required to do every three months here. Now the SQL server won't start; says there's a log-on failure. I have looked through all of the SQL administration applications and the help files and I cannot find a single word about changing the password!
Can anybody advise?
Thanks in advance!
Mark
|
|
|
|
|
I normally install SQL Server to log on as LocalSystem if I don't need domain support, or create a special user in the domain whose password does not expire.
To set the password for SQL Server's service, go to the Services tool (Control Panel, Administrative Tools, Services), right-click the MSSQLSERVER service and choose Properties. Go to the "Log On" tab and enter the new password (twice).
|
|
|
|
|
|
how to insert images into database table from enterprise manager using sqlserver.
|
|
|
|
|
do u want to send the images using a query???
sAqIb
"Our scientific power has outrun our spiritual power. We have guided missiles and misguided men."
Dr. Martin Luther King Jr.
|
|
|
|
|
Hi I am looking for a bit of guidance with an sql query I need to build a query to format my data in the following way
http://www.geocities.com/liverpool_anfieldreds/Images/target_results.JPG
but the data is in the following format using this sql
http://www.geocities.com/liverpool_anfieldreds/Images/current_results.JPG
Select IsNull(p.firstName+ ' ','') + IsNull(p.MiddleName+ ' ','') + IsNull(p.LastName,'') as 'Player',
rzd.Target,
rzd.Total,
DATENAME(WEEKDAY, rzd.Date) as 'Day'
From RedZoneData rzd
Join Person p on p.ID = rzd.PlayerID
Where rzd.PlayerID in (3 ,2)
And rzd.Date Between (Select StartDate From RedZoneWeeklyMapping
Where ID = 15)
And (Select EndDate From RedZoneWeeklyMapping
Where ID = 15)
Order By p.ID, rzd.Date
and im not really sure if its possible to change it around so the days are at the top and totals in their cols etc any help or advice would be appreciated thanks in advance
Tim
|
|
|
|
|
i am using c#.net and sql server2000 for my application.
i amable to do all operations with direct access over the database.
Now i am to connect to the server using Remote Data Service (RDS) and do all data base operations over this remote connection
please help me
|
|
|
|
|
I am using a stored procedure and returning rows to display in my report. I am grouping by a SOURCE field in the stored procedure. When the rows for this source are displayed on separate lines, I suppress the printing of the source on the subsequent lines by using the Previous function. In other words, if I have 5 rows, I print the source on the 1st row (occurrence) but not on rows 2-5. That works great. What I want to do now is sum the data columns for that source by adding a total line. I'm using the Previous function to check when the source changes, but I'm printing the total line on the first occurrence of the new source instead of the last (after row 1 instead of after row 5). I basically need to do a look ahead, determine how many rows I have, and then when I get the last row, print the total line. Is there a way of doing this within Reporting Services?
|
|
|
|
|
I have a table in an access database with a DateTime column. I have a query that is supposed to look for all rows with a specific date in the date column. The problem is, for two Date/Time objects to be equal in Access the time part must also match. I only want it to match on the date part (I use the time for something else so I can leave that out all together). I have tried this:
<br />
SELECT Log.id, Log.LibraryID, Log.Action, Log.Date<br />
FROM Log<br />
WHERE (((Log.LibraryID)=[libid]) AND ((DatePart("mm-dd-yyyy",Log.Date))=DatePart("mm-dd-yyyy",[checkdate])));<br />
But I get an error about how its typed incorrectly or is too complex to be evaluated. How due I match on only the Date?
|
|
|
|
|
DatePart("mm",Log.Date)+"-"+DatePart("dd",Log.Date)+"-"+DatePart("yyyy",Log.Date)
or use format function
|
|
|
|
|
I tried that and still got the too complex error:
<br />
SELECT Log.id, Log.LibraryID, Log.Action, Log.Date<br />
FROM Log<br />
WHERE (((Log.LibraryID)=[libid]) AND (DatePart("mm",Log.Date)+"-"+DatePart("dd",Log.Date)+"-"+DatePart("yyyy",Log.Date))=( <br />
DatePart("mm",[checkdate])+"-"+DatePart("dd",[checkdate])+"-"+DatePart("yyyy",[checkdate])));<br />
|
|
|
|
|
SELECT str(datepart('d',now()))+'-'+str(datepart('m',now()))+'-'+ str(datepart('yyyy',now()))
This worked for me. You can try format function as well.
|
|
|
|
|
ello,
I have a database that will need to be modified, i.e. adding columns, stored procedures, etc.
I can't create a backup as the database is live and will be continue to be updated with customer data.
My colleges what me to create the updates in code which I have done. However, my real problem is how do I know if the database has been updated and is current version.
I will not be updating the data that has been entered, only doing things like:
Alter [tableName] add Salary money null
create procedure dbo.insertPayment .....
example
if(currentVersion) then
'No need to update
Else
'Updates are available
'Add column
'Drop column
'create procedure
'etc
Is there any method that I could use to detect if the database is current or not.
I hope I have been clear on this.
Using VS 2005 & SQL Server 2005
Many thanks in advance,
Steve
|
|
|
|
|
steve_rm wrote: Is there any method that I could use to detect if the database is current or not.
That really depends on your idea of "current".
Before each ALTER statement I'd have an
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTableName'
AND COLUMN_NAME = 'ColumnName')
BEGIN
-- Add the column we checked for
ALTER TABLE ....
END
(The above code is for SQL Server 2000, there will be a similar equivalent in SQL Server 2005, but I don't know what it is)
You may also like to take a copy of your live database and practice your scripts on the copy first.
|
|
|
|
|
Hi,
I've a problem when using Carlos Antollini ADO class in a "realtime" application. What I do
is, that I call a thread that does all the database operations once every 10 minutes
or so. But it seems like the database operations are not called asyncron, because
the other threads within the application get freezed!?
Would be great if you could give advice, otherwise I've to rewrite the whole DB part
Thanks
Mario
m_hThread = (HANDLE) _beginthreadex(NULL,0,_ThreadWriteToDB,this, 0,&nDummy);
UINT WINAPI CDBManager::_ThreadWriteToDB(LPVOID pParam)
{
ASSERT(pParam);
CDBManager *pDBMan = reinterpret_cast<cdbmanager *=""> (pParam);
DosSetPriority(PRTYS_THREAD, PRTYC_IDLETIME, THREAD_PRIORITY_IDLE, 0);
pDBMan->m_Con.BeginTransaction();
...
...
...
pDBMan->m_Con.CommitTransaction();
// this thread has finished its work so we close the handle
CloseHandle(pDBMan->m_hThread);
}
|
|
|
|
|
hai,
in my application i want to create one trigger.
the situation is i 've two tables T1 and T2.
when i update T1 i want to insert the data before updated ie old values to table T2.
i used one trigger with 'instead of', but the pblm is T1 is not updated but values inserted to T2 perfromed fine.
any help is appreciated!!
All I ever wanted is what others have....
CrazySanker
|
|
|
|
|
crazysanker wrote: i used one trigger with 'instead of', but the pblm is T1 is not updated but values inserted to T2 perfromed fine.
Try a regular trigger, and not an instead of trigger.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
can u explain something in detail
All I ever wanted is what others have....
CrazySanker
|
|
|
|
|
If you do not explicitly insert the values into the table within an Instead Of trigger, they will not be inserted. You have it working where you put the old values into your T2 table...but the new values aren't being put into the T1 table. This is because you are using an Instead Of trigger, and you aren't inserting the inserted table values into T1. You could continue to use an Instead Of trigger (which doesn't make much sense), but you would need to do something like this:
INSERT INTO T1
SELECT Field1,Field2,Field3
FROM inserted
However, you could just use a regular trigger, and NOT an Instead Of trigger. Rather than this:
CREATE TRIGGER Trig_InsteadOfINS_T1 ON T1
INSTEAD OF INSERT
AS
--etc, etc.
Do this:
CREATE TRIGGER Trig_INS_T1 ON T1
AS
--etc, etc.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
I am following this article to import data from CSV file into my database table:
http://www.codeproject.com/aspnet/ImportExportCSV.asp[^]
The problem I am facing is that one of my table contains a money data-type column. Therefore, my application gives me Conversion error.
When I try to import data from csv file into this table, I get:
Disallowed implicit conversion from data type varchar to data type money, table 'MyTable', column 'Total'. Use the CONVERT function to run this query.
The above article that I am following, puts all the data values from the csv file into quotes, such as: Insert into MyTable...... Values ('value 1','23.34'...). Thus SQL cannot convert 23.34 into money.
Any way to solve this problem?
Thanks
|
|
|
|
|