Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dipankar
Starting Member
1 Post |
 Posted - 2002-04-11 : 05:46:54
|
| Hello all,I want to write a trigger on a table "member" for insert, update and delete which updates a field in a different table "online" when the field [member.isonline] is "0" (false)(bit type) and thus changes [online.messages] to "0" (bit type fileld). The common field on which the change is to be made should be [member_details.name] = [online.from_name]([online.from_name] is repetative) and [member.name] is the primary key.Help!!!!Thanx in advanceDipankar |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-04-11 : 09:36:22
|
First off, I had to read your post several times in order to deduce your schema. In the future, it is helpful if you post your ddl (create table) statements along with you question. While I am picking on you, tables have columns not fields and it is poor design to use reserved words (like name) as column names…Now, onto the helping part... Here is the ddl I came up with something like...create table member ( [name] varchar(50) not null primary key, isonline bit )create table member_details ( [name] varchar(50) null references member(name) )create table online ( from_name varchar(50) null references member_details(name) ) Now, given that I got that (mostly) correct, there are couple issues here. First, I am going to make the assumption that you don’t need the trigger ON DELETE because you will have some other process that removes the row in member_details and online for the member(s) being deleted, otherwise you would violate integrity. Second, this should be a pretty simple trigger to write. You should use the......IF UPDATE(isonline)... ...clause to identify a change in the isonline column. Then in that block you will update the online table with the value in INSERTED.isonline. To do this you will need to join member,member_details,online and INSERTED.Take a look at CREATE TRIGGER syntax in Books On Line to actually write the trigger. If you are still having trouble, post your actual DDL and your attempted (but failing) CREATE TRIGGER statement. We can help you debug it.<O>Edited by - Page47 on 04/11/2002 09:39:02 |
 |
|
|
|
|
|
|
|
Trigger problem - Help me