question with trigger

i have a table which has columns scheduledrent,actualrent beside other columns.
the actual rent is based on some calculations but uses scheduledrent for the calculations.

everytime there is a change to scheduledrent, the actualrent should also change. so if i write a trigger saying
if updated(scheduledrent)
do some calculations and change the actualrent..using an update stmt.

would it make the change only for that row or for all the rows in the table? if it does it for all the rows, how do i make it apply only to that row. how should i have my update stmt in such a case. my primarykey is a combination of 4 rows. so can i get all the 4 values into some variables and while i do the update do it on that condition ? or is there any easier way of doing it.

thanks.
D.Inside the trigger code you have access to a logical table named "inserted". You can run your update logic with that table and it will only update the rows that were updated. The logical table "inserted" is available for update triggers and, naturally enough, insert triggers.

The logical table "deleted" is available for delete triggers and shows the records to be deleted. In an update trigger the "deleted" table shows the original values before they are to be changed.|||ok it works if i change it to inserted.
have one q though. i have 10 ppl accessing the db at any time. so if all of them make an update to the db (not to the same record though) each one will have an entry in the logical inserted table. so how would the system differentiate between them, since they all login to sql server through the same asp.net account.

thanks for the tip.|||The inserted logical table will only be for the record(s) that triggered that specific update. So everyone will have their own inserted logical tables and won't be stepping on one another.|||oh so sql server creates an inserted table for each user ?
but how does the sql server differentiate between users ?

lets say userA logged onto mysite and made an update to a row. the row gets inserted into the inserted table. userB also logs in at the same time and makes an update to another row. the new row also gets into the inserted table. now both these users are logged into the sqlserver through the same ASP.NET account. so how does sql server differentiate betwen userA and userB and their updated rows?

thanks again.|||It's a "logical" table meaning that it acts like a table but isn't really one. It is there to allow the trigger code to inspect the state of the old and new records so that the trigger code can take whatever action is appropriate. The logical table inside one trigger event isn't visible or accessible to the logical table in any other trigger event, even for the same user logged on multiple times simultaneously updating different record(s).|||i dont think i have understood completely. do you know any article that xplains in detail how the inserted table actually works...?
thanks anyway. will leave it for now. hope it works. will get back incase i have any prb.

thanks McMurdoStation.sql

Question related to Data Import

Hi,

I have a scenario where I either have an option of writing a VB based import routine to import "FORMATTED" data in a text file and populate it in my underlying tables (SQL2K). However someone suggested that I should use DTS.

I am not very familiar with DTS and would therefore appreciate:

1. What would be a better solution - using the VB based import routine that I was planning to have in place or use a DTS?

2. What are the benefits of using a DTS?

3. How easy is it to define/develop a DTS?

4. Aee DTSs flexible?

5. Can someone recommend a URL from where I can read more about DTS and ideally with an example which I can follow to define/develop a DTS myself?

I'll appreciate your reply. Thanks.DTS is the way to go. DTS is very functional in transforming data. Read this link for more info:
http://www.databasejournal.com/features/mssql/article.php/3086891

and also look up DTS in Books Online, and the internet

Hope this helps|||You need access to enterprise manager (to make life easier)

sql server's client side tools...

But why not use BULK INSERT with a Format file? Or bcp

I've seen DTS do wierd things...but that was back in 7.

And if your VB routine was going to do singleton inserts, I would recommend against taht...

Holy Tranny log batman...|||Thanks for your replies. I'll try out the DTS option. I am not clear what you mean by "bcp".

I have one question (for now) related to the DTS option:

If I go with the DTS approach, can I define/develop (I do know what is the correct terminology) a DTS package and then "DEPLOY" at the the user's side so that everytime the user receives a text file with a similar format he can simply "RUN" the DTS package without requiring any help?

My second related question is that if I want to use the DTS deplyment option at the end-user's machine, would that require the Enterprise Manager to be installed at the end-user's machine or can he simply invoke it in some othr manner. I have a feeling (based on my limited knowledge) that the user would require the Enterprise Manager to be installed on his/her machine but I want to confirm.

As you might have figured out, I want to put in place a solution which I can "deploy" and then after a litle bit of training, let the user(s) import the data without my involvement.

I'll appreciate your feedback based on the above.

Thanks.|||Originally posted by Joozh
Thanks for your replies. I'll try out the DTS option. I am not clear what you mean by "bcp".

I have one question (for now) related to the DTS option:

If I go with the DTS approach, can I define/develop (I do know what is the correct terminology) a DTS package and then "DEPLOY" at the the user's side so that everytime the user receives a text file with a similar format he can simply "RUN" the DTS package without requiring any help?

My second related question is that if I want to use the DTS deplyment option at the end-user's machine, would that require the Enterprise Manager to be installed at the end-user's machine or can he simply invoke it in some othr manner. I have a feeling (based on my limited knowledge) that the user would require the Enterprise Manager to be installed on his/her machine but I want to confirm.

As you might have figured out, I want to put in place a solution which I can "deploy" and then after a litle bit of training, let the user(s) import the data without my involvement.

I'll appreciate your feedback based on the above.

Thanks.

Once you create the DTS package, make sure it's portable, ie, it's can be used on virtually any computer. Read the Following article portability of DTS packages, it will give you some idea.

http://www.sqlservercentral.com/columnists/kfeit/portabledtspackages.asp

Secondly, once the DTS package is completed you can schedule a job for the package. This job can run on any server. So you can transform/format files or load the data from files to tables etc. So the user would just see the finished product of the DTS packages.

Hope this helps.

question on SQL schema

My company is currently developing a new database driven
application. Originally we had designed the structure
with three database's (based on update schedule and
administration). My Oracle DBA has looked over the plan
and recomends we put the three databases into one and
seperate as multiple schemas. I have looked all over
microsoft.com and haven't found anything that discusses
multiple schemas. I'm guessing its a different
terminology on the Microsoft side.
How would I go about developing a single database that is
segregated into three so called schemas. I know Oracle
uses [schema.]tablename and SQL uses
server.database.owner.tablename. Does this mean I create
an owner for each dataset (schema).
thanks
JustinHi Justin,
I think you are right. Oracle uses [schema.]tablename and SQL uses
server.database.owner.tablename. You can create an owner for each dataset
(schema).
You may want to know what is "owner" in SQL Server, please refer to "Owners
and Permissions", "Database Object Owner" in SQL Books Online.
~~~~~~~~~~~~~~~~~
Every object in Microsoft SQL Server 2000 is owned by a user. The owner is
identified by a database user identifier (ID). When an object is first
created, the only user ID that can access the object is the user ID of the
owner or creator. For any other user to access the object, the owner must
grant permissions to that user. If the owner wants only specific users to
access the object, the owner can grant permissions to those specific users.
For tables and views, the owner can grant INSERT, UPDATE, DELETE, SELECT,
and REFERENCES permissions, or ALL permissions. A user must have INSERT,
UPDATE, DELETE, or SELECT permissions on a table before they can specify it
in INSERT, UPDATE, DELETE, or SELECT statements. The REFERENCES permission
lets the owner of another table use columns in your table as the target of
a REFERENCES FOREIGN KEY constraint from their table. The following example
illustrates granting SELECT permissions to a group named Teachers and
REFERENCES permissions to another development user:
GRANT SELECT ON MyTable TO Teachers
GRANT REFERENCES (PrimaryKeyCol) ON MyTable to DevUser1
The owner of a stored procedure can grant EXECUTE permissions for the
stored procedure. If the owner of a base table wants to prevent users from
accessing the table directly, they can grant permissions on views or stored
procedures referencing the table, but not grant any permissions on the
table itself. This is the foundation of the SQL Server mechanisms to ensure
that users do not see data they are not authorized to access.
Users can also be granted statement permissions. Some statements, such as
CREATE TABLE and CREATE VIEW, can only be executed by certain users (in
this case, the dbo user). If the dbo wants another user to be able to
create tables or views, they must grant the permission to execute these
statements to that user.
~~~~~~~~~~~~~~~~~
Sincerely,
William Wang
Microsoft Partner Online Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--
| Content-Class: urn:content-classes:message
| From: "Justin King" <jusking@.firstam.com>
| Sender: "Justin King" <jusking@.firstam.com>
| Subject: question on SQL schema
| Date: Tue, 12 Aug 2003 20:58:23 -0700
| Lines: 19
| Message-ID: <090d01c3614f$23f86d60$a401280a@.phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcNhTyP4GoQlr1/wQteBwfzcYZvj7Q==| Newsgroups: microsoft.public.sqlserver.server
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:300961
| NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| My company is currently developing a new database driven
| application. Originally we had designed the structure
| with three database's (based on update schedule and
| administration). My Oracle DBA has looked over the plan
| and recomends we put the three databases into one and
| seperate as multiple schemas. I have looked all over
| microsoft.com and haven't found anything that discusses
| multiple schemas. I'm guessing its a different
| terminology on the Microsoft side.
|
| How would I go about developing a single database that is
| segregated into three so called schemas. I know Oracle
| uses [schema.]tablename and SQL uses
| server.database.owner.tablename. Does this mean I create
| an owner for each dataset (schema).
|
| thanks
|
| Justin
|

Question on filter itemset by up to one attribute in Association Rules

Hi, all experts here,

Thanks for your kind attention.

I want to filter the itemsets or rules based on more than 2 attributes, how can we achieve that? (I can only filter them by only one attribute?). Is it possible to achieve that?

Thanks a lot and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

The filter actually can specify a regular expression, using the .Net language for regular expressions (the RegEx class).

Assume that you want any items set that include the Helmets and Fenders attributes, in any order

Here is an example of such filter:

((.*Helmets.*Fenders.*)|(.*Fenders.*Helmets.*))|||

Hi, are you saying this can be achieved in the association rules viewer?

Thanks.

|||Yes. In the filter, type directly the regular expression|||

Hi, Bogdan,

Thanks a lot.

Best regards,