|
yes it is sql server, and i will have to do some research on these triggers you speak of, any direct links, good books, ideas you can share?
Dim Beautiful As String
Beautiful = "ignorant"
Label1.Text = "The world is full of " & Beautiful & " people."
Why is common sense such an un-common comodity?
|
|
|
|
|
The most complete documentation would be in SQL Sever Books On Line, which can be downloaded here.[^]
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
Cool, I'll check it out, thanks!
|
|
|
|
|
ok, so i've done some reading on triggers, and now i've got some questions!!!
do i want to do this with an instead of, or after statement?
or would it be just best for me to use this: CREATE trigger Nnew_updates on people for insert, update as
if i use this one, will i need to search thru the deleted table to find it, then rollback, or just use the instead of and compare the requested change with the trigger and not let it do it at all? (can i even do that with the instead of)
I found some DB2 info that uses BEFORE, too bad sqlSever does't use that, it'd be nice.
Course, the info that i found showing sql doesn't use it was from 2000, and it said that Microsoft was contemplating the use of BEFORE, do you konw if they have implemented the use of it in sp3 by chance?
My next question then is regardless of what method I go with what should my trigger statement look like? for instance I'm already looking at using what i pasted earlier and i've got the format down, but what should my begin statement look like?
I need to beable to compare the old access_level to the new access_level. so will i need to compare the deleted table with the people table? or is there an easier way to just not allow changes to the access_level column?
Dim Beautiful As String
Beautiful = "ignorant"
Label1.Text = "The world is full of " & Beautiful & " people."
Why is common sense such an un-common comodity?
|
|
|
|
|
I would use separate triggers for insert and update. The Deleted pseudoTable will not exist in the case of an insert, so the handling will likely need to be different. In both cases the new data can be accessed in the "Inserted" psuedoTable.
The for insert, for update triggers would seem nore sensible here than an after or instead of...
Could be as simple as "if Inserted.Access_level <> Deleted.Access_level then rollback transaction"
For inserts, I suspect you will want to check the user that is inserting the record (not the user being added)
- SELECT user_name() will return this, but this might be probematic if the application is always logged in as sysadmin - dbo - (it shouldn't be, but that is another matter).
Yuo should likely disallow changes to more than on record at a time (rollback if @@rowCount > 1) as this would seem inappropriate in any case, and allows succeeding checks to assume only one row (the current one) in the psuedo tables.
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
Well, my thinking is that I'm going to be better off by not restricting it to users as there IS one user who has admin access to the software but does not need the access to change others access(i was forced to give him access). so in other words i need to restrict the access to the access_level column entirely, and IF i need to update someones level(only if my boss tells me to) then i disable the trigger to complete what I would need to do. plus the domain levels are not set to change the db level so they can log in with sa level access.
and i think i would need to block any new entries as admin as there is nothing keeping someone from entering a new username and creating it as an admin from the db.(not sure if that was inline or out-of-line with what you were saying about the inserts.)
in your last statement, not sure i fully understand, are you saying i need to block more the possiblity of someone using a query to insert multiple accounts so as to trick the trigger into only stopping the first entry?
and i sould be able to run both the insert and update checks in the same trigger right? by just separating the functions of each rather than running them as a single statement?
Dim Beautiful As String
Beautiful = "ignorant"
Label1.Text = "The world is full of " & Beautiful & " people."
Why is common sense such an un-common comodity?
|
|
|
|
|
wetrivrrat wrote:
and i sould be able to run both the insert and update checks in the same trigger right? by just separating the functions of each rather than running them as a single statement?
You could, but the code might be much simpler if you did them as separate triggers, as they will fire independantly (the 'for update' won't fire the 'for insert' unless the update trigger itself inserts new records).
wetrivrrat wrote:
and i think i would need to block any new entries as admin as there is nothing keeping someone from entering a new username and creating it as an admin from the db.(
That makes the for insert trigger simple: just rollback transaction
wetrivrrat wrote:
in your last statement, not sure i fully understand, are you saying i need to block more the possiblity of someone using a query to insert multiple accounts so as to trick the trigger into only stopping the first entry?
I was more concered about an update that changed the access_level for any username 'like A%" forinstance, or all usernames ....
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
Which is better?
sqlDataReader[i]
sqlDataReader["columnName"]
sqlDataReader.GetValue(i)
where i is index of column
|
|
|
|
|
i prefer using the column name, for the simple reason that it is more readable than using a column index...
moreover, it would avoid some debug mistakes...
TOXCCT >>> GEII power
[toxcct][VisualCalc]
|
|
|
|
|
sqlDataReader["columnName"]
is generally used....
coz giving number as index may create prob if the sequence of columns is changed
Annpurna Tiwari
IIPL Company
Nagpur
India
|
|
|
|
|
Hi, i just wanna download DB2/ORacle editor in my system having the OS windows 2000.How can i download this freely in my machine.Plzz help me.
Thanks
BhuvanaSathish
|
|
|
|
|
oracle databases are free unless you use the DL versions for sell and some reasons explained on the website....
did you have a look at their site ?
TOXCCT >>> GEII power
[toxcct][VisualCalc]
|
|
|
|
|
I have SQL Server 2000 installed on my laptop, as well as Visual Studio .NET 2003. I am going through a tutorial in which it asks me to go to the Northwind database and right-click on Views and select New View. When I right-click on Views, the only context menu items I get are Refresh and Properties. Does anyone know why I do not get the "New View..." menu item in Server Explorer within Visual Studio .NET? Thanks in advance.
Dan
|
|
|
|
|
I'd suspect that you don't get all the options you would get in Enterprise Manager from within the IDE.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
Check your are db owner on the security panel for the database. You may not have rights to create a view.
|
|
|
|
|
Upon further investigation, it turns out that Visual Studio.NET Professional visual data tools (via Server Explorer) do not work with SQL Server 2000, but only with the SQL Server Desktop Engine (MSDE). This is actually on the features page for Visual Studio, but who looks at that, right? 
So, here's what I did... I left SQL Server 2000 on my laptop, and installed the MSDE (they can reside together). Some good instructions for doing this are here. After doing this, you can use the Server Explorer to create a connection to the MSDE and create tables, views, etc.
Another problem I ran into was the tutorial that I was going through had examples using the Northwind sample database, which comes with SQL Server, but not with MSDE. I used the Data Transformation Services (Import/Export Wizard) to copy the Northwind database from SQL Server 2000 to my MSDE.
|
|
|
|
|
I need to get the relationships that exist between the tables in a database. The database structure is unknown to me , i.e at runtime the program connects to any database be it Access or SqlServer. From that database i get the info. related to the tables such as the columns that exist in it, their datatypes,etc but not the table relationship to other tables. For SQL databse i have used this approach - I have used SQLDMO to get the table names. Then generating a select statement e.g: "SELECT * FROM Tablename" and then using DataAdapter.FillSchema method i get the structure of the table. But i also need the realtionships that exist between the tables. Could any one help me??! (Using C#)
|
|
|
|
|
I assume you have access to enterprise manager.
Look for any database diagrams. If there are none add the tables you need into a new database diagram and the foreign key relationships will show themselves.
|
|
|
|
|
Usually you need to access the metadata tables of the database to get this information.
There's no standardized way to get this information. For example, Access stores tables in a table MSysObjects and relationships in a table MSysRelationships, but SQLServer will have a different system.
Regards,
mav
|
|
|
|
|
You can query foreign key relationships from the INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS view. You could also get the schema from the INFORMATION_SCHEMA views. Look up INFORMATION_SCHEMA in SQL Server Books Online.
|
|
|
|
|
I have a matter with Ms.Access. I don't know how to pass the value from one form to another form. Also, I want to set this value to the text-box of the destination form.
For example:
I'd like to transfer the value '5' inside the code VBA of the form 'form1' to the form 'form2'. After moving successfully, I want to show this value in a text-box of the destination form. Please point me to a way of solutions.
Thanks so much...
|
|
|
|
|
Suppose you have two forms and they are Form1,Form2. Form2 is your destination form. Where you want to pass a value 5 to Form2's textbox. The following code should work.
Dim form2 as New Form2
form2.TextBox1.Text = "5"
Tutul
|
|
|
|
|
I have two colums, WPID and WNum. WPID is distinct, but WNum can be reapeated.
For instance, I could have WPID 1-10, and each value for WNum would be 55555. What I need to do is get the maximum value for WPID associated to WNum, in this case 10.....and spin through all the records this way.
I've tried (and other ways):
SELECT MAX(WPID) AS Expr1, [WNum]
FROM WP_General_Info
GROUP BY [WNum], WPID
...but it's not working.
|
|
|
|
|
SELECT TOP 1 WPID, WNum
FROM WP_General_Info
ORDER BY WPID DESC
|
|
|
|
|
Thanks knarf_scot! That helps!
I'm basically using this for a SQL Server Report, and am having trouble with the footer (which shows the highest/latest value for WPID). I have my grouping right and can see my latest value for WPID, but now the values in the textboxes aren't showing for the max value of the footer.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
