|
I have a trigger that I want to fire if:
1. A certain Column was Updated ... OR
2. The record was deleted
The code that should run in both respects is the same block of code.
What I have at the moment is:
ALTER TRIGGER [trgFlag] ON [dbo].[tblStockItems]
AFTER DELETE, UPDATE
AS
IF UPDATE(Result1) OR UPDATE(Result2)
BEGIN
<All my code to execute comes here.....>
END
But... what I need is something like this:
ALTER TRIGGER [trgFlag] ON [dbo].[tblStockItems]
AFTER DELETE, UPDATE
AS
IF UPDATE(Result1) OR UPDATE(Result2) OR DELETED
BEGIN
<All my code to execute comes here.....>
END
Kind Regards
modified on Monday, May 4, 2009 7:59 AM
|
|
|
|
|
Can you not put <All my code to execute comes here.....> in a SP and then have separate triggers for update and delete which call the SP?
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
See, the thing is, I want to make it column specific, but I didn't know how to to test to see when a record has been updated or deleted, since an update action fires a delete, then an insert.
So in the end I got it working by doing the following:
IF Update(col1) OR Update(Col2) OR NOT EXISTS(SELECT * FROM INSERTED)
This works because, during an update, DELETED and INSERTED has data, but during a delete, the INSERTED returns NULL.
Problem solved!
Thanx for your input Henry! - there I go answering my own questions again, I'm a little CP-trigger happy! 
|
|
|
|
|
evilnoodle wrote: there I go answering my own questions again
If only all posters would do this.
Excellent sleuthing, I've bookmarked this. It's bound to be useful. 
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Dear All,
I have a query that has some parameters:
(
SELECT DISTINCT
T.TICKET_NUMBER AS TicketNum, A.DESCRIPTION AS Action, O.SYMBOL_CODE AS Symbol, T.TRADE_PRICE AS Price, T.VOLUME_TRADED AS FillVol,
T.EXTENDED_PRICE AS TotalValue, T.SUBMITTED_TIME AS ActTime, T.SUBMITTED_DATE AS TransDate
FROM TSDETL AS T INNER JOIN
TSORDR AS O ON O.SUBMITTED_DATE = T.SUBMITTED_DATE AND O.TICKET_NUMBER = T.TICKET_NUMBER INNER JOIN
TSORDA AS A ON O.ORDER_ACTION = A.ACTION_CODE
WHERE (T.SUBMITTED_DATE = @SUBMIT_DATE) AND (T.VOLUME_TRADED >= @Volume) AND (T.TICKET_NUMBER IS NOT NULL) AND (O.SYMBOL_CODE= @Symbol)
ORDER BY ActTime DESC
)
I passed the three parameters (@SUBMIT_DATE, @Volume, @Symbol) successfully, but I want to read the operator (>= or <=) as a parameter...I have a combo box with <= and >=, and I want to pass the value of this combo into the query.
How can I do this???
this is because the operator may be >= or <=, and I don't want to write two queries.
Kind Regards
OBarahmeh
|
|
|
|
|
Answered in the C# forum, please don't cross-post.
I are troll 
|
|
|
|
|
Would you like an ugly option? How about:
CREATE FUNCTION DoFunction
(
@Op1 BIGINT
,
@Oper NVARCHAR(2)
,
@Op2 BIGINT
)
RETURNS BIT
AS
BEGIN
IF ( @Oper = '>=' AND @Op1 >= @Op2 ) RETURN 1
ELSE IF ( @Oper = '<=' AND @Op1 <= @Op2 ) RETURN 1
-- More
RETURN 0
END
select * from test where dbo.dofunction ( VOLUME_TRADED , '<=' , @Volume )=1
Otherwise, I might suggest:
SELECT * FROM test WHERE CASE WHEN @Oper='<=' AND VOLUME_TRADED <= @Volume THEN 1 WHEN @Oper='>=' AND VOLUME_TRADED >= @Volume THEN 1 ELSE 0 END = 1
|
|
|
|
|
Hi Friends,
I need an sql query help.i need to implement distinct staetement in my select query.I need the Assets.AsstId should be distinct in query.i attach the query below .please help me i need its urgently.The bolded value should be distinct.
SELECT aspnet_Users.UserName, AssetsSettingsTypes.AssetSettingsName, CONVERT(varchar(MAX), Assets.AssetShortDescrition)
AS AssetShortDescrition,Assets.AssetId, '<img src="' + CONVERT(varchar(MAX), Images.ThumbUrl) + '" width="100" height="100"/>' AS Image,
dbo.GetFriendlyDateTimeValue(UserAssets.CreationTime) AS TimeDiff, UserAssets.CreationTime as CreationTime,
'profileUser/Profileview.aspx?' + CONVERT(varchar(MAX), aspnet_Users.UserName) AS UrlProfile,
'profileUser/userPhotos.aspx?AssetId=' + CONVERT(varchar(MAX), UserAssets.AssetId)
+ '&UserName=' + aspnet_Users.UserName AS NavUrl, 'forntend/Commentview.aspx?' + CONVERT(varchar(MAX), UserAssets.AssetId)
+ '&' + CONVERT(varchar(MAX), UserAssets.UserId) AS UrlComment, '(' + CONVERT(varchar(MAX),
(SELECT COUNT(*) FROM UserComments WHERE (UserAssetId = UserAssets.UserAssetId)))+ ')' AS CommentCount
FROM AssetsSettingsData INNER JOIN
Assets ON AssetsSettingsData.AssetId = Assets.AssetId INNER JOIN
AssetsSettingsTypes ON AssetsSettingsData.AssetSettingsTypeId = AssetsSettingsTypes.AssetSettingsTypeId INNER JOIN
Images ON Assets.AssetId = Images.AssetId INNER JOIN
UserAssets ON Assets.AssetId = UserAssets.AssetId INNER JOIN
aspnet_Users ON UserAssets.UserId = aspnet_Users.UserId
WHERE (aspnet_Users.UserId = '9D4C2631-BB10-47C0-A4EE-5B1E92FE1AB9')
|
|
|
|
|
While Deploying the SSRS 2008 Project I am getting a "Reporting Services Login" Dialog box, which does not accept any logins. I have tried Windows , SQL server Login credentials but did not get thru it.
|
|
|
|
|
I had this problem recently also. I made two corrections in the Project properties. In the Data Source and target folder sections, I had included the http:// and the reporting server name and reports folders. I deleted those so they just started at the project name. Finally on the target url, I changed it to http:// the reporting server name and /reportserver. That fixed the problem for me. Good luck
|
|
|
|
|
Thanks dirty12,
I have tried this but it did not work, right now I have TargetServerURL as "http://localhost/reportserver". All I want to know is What is this "Reporting Services Login" Dialog and why doesn't it accept any Login credentials.
|
|
|
|
|
Sorry, I don't have a clue as to why this was happening. Although I had the same problem when I tried "localhost" and when I actually put the server name in, the problem went away. Good Luck.
|
|
|
|
|
I had the same problem. It was complicated by the fact that I have two instances of SSRS running on my system one is 2005 the other 2008, and I thought I knew the 2008 instance's name. I had accidently typed the service account name when I actually needed to type in the Virtual Directory.
I resolved this by finding the exact name I needed. I started Reporting Service Configuration Manager, conected to my instance, clicked on the "Web Service URL" to find the correct name. In my case, I needed to replace http://computername/Reportserver$MSSQL10/ with http://computername/Reportserver_MSSQL10/. This now allows me to deploy (without prompts).
-Will
|
|
|
|
|
Hi All,
I have tables called CALL and Operator.
Select Distinct(Mobile),MIN(DATE_OF_CALL),NAME_OPERATOR from Call inner Join Operator on
Call.CustomerId=Operator.CustomerID
where Datename(month,DATE_OF_CALL)='APRIL'
Group by Mobile
What i want is to get the first date of entry for each Mobiles in the month of april and the operator who attended them.
However i Have to group by Name_operator to run the above script.But i dont want them to be grouped by OPerator.I want the first entry of a mobile in the database together with the name of the operator who attended them.
How do i go about it Please?
Thank you so much.
|
|
|
|
|
Presumably you are using SQL 2005+ as you database (I know it may be an unfounded assumption)
Look into Row_Number and partition. These will allow you to select the records for month# with a rownumber partitioned over mobileNo ordered by date of call having a rownumber = 1
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Than you very much.Its most appreciated.I am Using SQL 2005.
Could you please give me an example please? What i want is the first entry of a mobile number for a certain month.I have the operator name in a diffrent table called Operaor.
For instance a mobile number 12 has called mike on 2009-04-01 and andy on 2009-04-02. I want only to get the first entry that is
Mobile:12 DateofCall :2009-04-01 : operatorName: Mike.
That is the first entry for this number on the month of april.
How could i do that on the above script? Please if you can give me an example.
Thank you onece again
|
|
|
|
|
HI I got an error with this one ?
Can any one advice?
Select distinct(Mobile), ROW_NUMBER() OVER(PARTITION BY mobile ORDER BY dateofcall DESC ) AS 'RowNumber' from Call
having RowNumber=1
Error :Invalid Column RowNumber
|
|
|
|
|
|
I'd do something like this:
SELECT [Call].*
,Operator.Fullname
FROM
(SELECT [Caller],FirstCall=MIN(CallTime) FROM [Call] WHERE DATEPART(MM,CallTime)=4 GROUP BY [Caller]) T
INNER JOIN [Call]
ON T.[Caller]=[Call].[Caller]
AND T.FirstCall=[Call].CallTime
INNER JOIN [Operator]
ON [Call].Operator=Operator.ID
Just be sure to have indices on Caller and CallTime.
|
|
|
|
|
 How to Show Vertical Text in SQL Server 2008 Reporting Services, Since it is possible in SQL Server 2005 Reporting Services. In SQL 2005(SSRS) Textbox has a property for Rotating and showing the text verically, but in SQL 2008(SSRS) does not any such property. Kindly help.
|
|
|
|
|
The textbox property names are Writing Mode (Horizontal/Vertical) and Direction (LTR/RTL).
|
|
|
|
|
|
1) Application looks good and usefull
2) This post is the worst advertisement I seen in weeks
I are troll
|
|
|
|
|
|
Following points should be considered while doing the performance comparison between normal sql query and sql views:
(i) Executing a View creates an extra effort for SQL Server for looking into system tables.
(ii) Improper implementation of JOIN and UNION can create performance bottlenecks in views.
Regards
Saanj
Either you love IT or leave IT...
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
