Creating Procedures and Triggers on SQL 2000
-
TWO HARD QUESTIONS FOR YOU! (aren't you so excited?)
I need to know how to create a PROCEDURE that will output the mean and standard deviation of a table, which I created with the name Account, and then output the results to me.
Second, this is the harder one, I need to create a table named ActionHistory consisting of two columns, TableName and ActionTime, then create a TRIGGER that will write the name of the previous table "Account" into the TableName portion, and the time of the change/update into the ActionHistory portion, whenever there is a change/update made to the Account table. To sort of keep track of all the action made to the Account table, so if someone were to manipulate it, we'd know which table (I will be creating this for multiple tables) as well as WHEN (datetime).
PLEASE HELP!!!!!
THANKS.
- Rani
-
Answer(s) to the first question are the AVE and STDEV functions. Look them up in BOL. That should provide you with what you want. (Unless you need code - in which place we would need a simple table-layout and what field(s) you need these values calculated).
It is too late in the evening for me to look up the second question, but I imagine that one or two of the system tables or sp's will work for you. There are LOTS of smart people on this site who will probably get you an answer quickly...
I wasn't born stupid - I had to study.
-
In regards to the second question,
"To sort of keep track of all the action made to the Account table, so if someone were to manipulate it, we'd know which table (I will be creating this for multiple tables) as well as WHEN (datetime). "
Wouldn't this information be a bit pointless without including who performed the change and what they changed?
Do a Google for "SQL Server Auditing" and you're sure to find a few relevant links. Actually you could probably just search this site. I'd be surprised if there wasn't something in the script library for this.
--------------------
Colt 45 - the original point and click interface
-
There was a recent article by Leo Peysakhovich on SQL auditing - you shall be able to find it easily. Also, there was a follow-up discussion with some good ideas thrown. In a summarised form, Leo's approach was to have a tool which will create triggers on all concerned tables. The triggers would write data changes to corresponding audit tables (each data table has its own audit table). The approach I recently used was to write data changes to a generic single audit table. Which approach to select is fully dependant on the specific requirements of the project.
All said above relates to auditing data changes. Auditing metadata changes is a bit trickier (actually, a lot trickier).
-
<<
I need to know how to create a PROCEDURE that will output the mean and standard deviation of a table, which I created with the name Account, and then output the results to me.
>>
I am glad you didn't ask about the median value. This would be a good puzzle, which has several known solutions. Most of them are very slow and based on row numbering, which is very unnatural in SQL2000, but is much better in SQL2005 with the new ROW_NUMBER() function added to TSQL. Check
Itzik Ben-Gan 's article on SQL row numbering at http://www.windowsitpro.com/SQLServer/Article/ArticleID/42302/42302.html
-
I was surprised there wasn't a median function already in SQL Server, or at least none that I could find easily. The solution I used was to create a table variable inside the stored procedure, with one column being of type INT INDENTITY. I insert the values I want into the table variable in order of lowest to highest value. This could be done with an INSERT statement that retrieves the data from table(s) of your choosing.
Then select the count of rows that wound up in the table variable. THEN retrieve the row in the middle of the table. (Though technically the true median will be the average of the middle two values in the table if there isn't an odd number of values)
The logic was a bit more complicated than I had hoped for but the performance is excellent with the small dataset I have (a few thousand records at the most).
-
David,
This method is incorrect - SQL Server does not guarantee that the identity values will be assigned in the order given in the ORDER BY clause. According to Itzik Ben-Gan, this issue has been raised with SQL Server development team and they confirmed it as a feature (not a bug). As far as I know in SQL2K5 it is still not guaranteed, however it becomes irrelevant because there is a new ROW_NUMBER() function.
-
Actually Farrel, I WOULD need coding since its been so long since I can remember anything. I'm not sure why my boss would not want to know which user last accessed the table Account, but thats just the sample he is giving me as a test. Maybe he'll ask a more difficult question later. Can you please provide me with the coding, because what I have is not working. You said to provide a table so here it is:
Here is the 'Account' table I made earlier:
create table Account(Account_Number varchar(6)NOT NULL PRIMARY KEY, Branch_Name varchar(12)NOT NULL REFERENCES Branch (Branch_Name), Balance money)
insert into Account values(10101, 'First Bank', 5500)
insert into Account values(20202, 'US Bank', 3550)
insert into Account values(30303, 'Commerce', 7550)
-------------------------------------------------------------------------
Now I need to output the Mean and Standard Deviation of the above Account. So I tried this code but I'm not sure if its right since when I Analyze it, some values come out as 'null'.
CREATE PROCEDURE project2question2 AS
DECLARE @Mean MONEY
DECLARE @Deviation MONEY
SET @Mean = (SELECT avg(cast(Balance as float)) as mean from Account)
SET @Deviation = (SELECT Balance, STDEV(Balance) st_deviation
FROM Account
GROUP BY Balance)
-------------------------------------------------------------------------
The second question was to create a Trigger that would output the name "Account" and the time of change/update to it. So I had this so far, but I KNOW its not right, so can anyone revise this for me? I basically need to create a trigger named ActionHistory that would have two columns: TableName and ActionTime. They should tie to my Account table so that when it was changed/updated then the name 'Account' would appear under TableName in my trigger, and the datetime of update would appear under ActionTime. But I just can't figure this one out.
Create table ActionHistory(TableName varchar(12) NOT NULL, ActionTime datetime NOT NULL)
CREATE TRIGGER trigger_ex2
ON ActionHistory
AFTER INSERT, UPDATE
AS
BEGIN
UPDATE ActionHistory
SET Account = UPPER(LName) WHERE TableName in (SELECT TableName FROM ActionHistory)
SET Loan = UPPER(LName) WHERE IDN in (SELECT TableName FROM ActionHistory)
-------------------------------------------------------
If someone could give me these codes I would be very grateful. I know you guys are the SQL wizzards. Thanks. (by the way, I use SQL 2000 edition)
- SOnia
-
Sonia - I think you are overthinking your first question. Look at the following:
DECLARE @account TABLE( Account_Number varchar(6) NOT NULL PRIMARY KEY,
Branch_Name varchar(12) NOT NULL,
Balance money)
INSERT INTO @account VALUES( 10101, 'First Bank', 5500)
INSERT INTO @account VALUES( 20202, 'US Bank', 3550)
INSERT INTO @account VALUES( 30303, 'Commerce', 7550)
-------------------------------------------------------------------------
SELECT AVG( Balance) AS Mean, STDEV( Balance) STD_Deviation
FROM @account
I think this should get you what you want...
As for the second part, I would recommend you look into the articles recommended to you.
I wasn't born stupid - I had to study.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply