Newbie needs urgent help, simple question 
Author Message
 Newbie needs urgent help, simple question

Hi, I am a newbie and have some basic questions..

Trigger question:
I have several tables, .. let's say table 1, 2 and 3

CREATE TABLE table1 (Id INT PRIMARY KEY, Deleted BIT DEFAULT 0)

CREATE TABLE table2 (ParentId INT REFERENCES table1(Id), Deleted BIT DEFAULT
0)
CREATE TABLE table3 (ParentId INT REFERENCES table1(Id), Deleted BIT DEFAULT
0)

I wish to make a trigger on table1 so that if a record in table1 is updated,
so that Deleted=1, table2 and table3 will also be updated with Deleted=1
where table2.ParentId=table1.Id.
If however a record in table1 is DELETED, so are the records in table2 and
table3 which have it for ParentId.

Something like
CREATE TRIGGER tr_table1 ON table1 FOR UPDATE,DELETE
AS
IF UPDATED(Deleted)
BEGIN
    IF (Deleted=1)
        UPDATE table2 SET Deleted=1 WHERE table2.ParentId=updated.Id
        UPDATE table3 SET Deleted=1 WHERE table3.ParentId=updated.Id
END
ELSE
BEGIN
    DELETE table2 WHERE table2.ParentId=deleted.Id
    DELETE table3 WHERE table3.ParentId=deleted.Id
END

I don't know the exact syntax.. also, I don't know if updated has alread got
the new values (right after it's updated) or just before it is changed.

Second question..
If I have a table like table1 above, but Id has the values 1, 2, 3, 4, 5,
10, 11, 12
what is the easiest way to find the Id's that are not used.. in this case 6,
7, 8, 9.. so I want numbers between min and max values that do not exist.
I can do a loop 1-12 and test each one of them, but it is slow, and I wonder
if there is a simple select statement for that.

Also, lets say I have 2 tables, that have a column 'Id' with values 1, 2, 3,
4, 5 and another table that has 1, 2, 4. How can I get the difference of the
two.
Right now I have something like
SELECT Id FROM table1 WHERE NOT EXISTS(SELECT Id FROM table2 WHERE
Id=table1.Id)
Is there a better way?

And last but not least.. It is possible to select something into a table
with SELECT INTO sometable FROM table WHERE ...
But is it possible to create a table into another DATABASE? Id not, how can
I copy or move a table from one database to another database?

Thanks...
Lisa



Mon, 19 Apr 2004 01:09:55 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. urgent simple help needed

2. URGENT HELP NEEDED! Simple Q on Triggers

3. Newbie - Urgent help needed.

4. Stupid newbie needs urgent help =)

5. 3 Simple Questions PLEASE HELP URGENT

6. URGENT! Newbie need help on graphics

7. 3 Simple Questions PLEASE HELP URGENT

8. Newbie needs help on simple problem

9. Newbie needs help with something simple!

10. URGENT ! HELP NEWBIE plz - easy dialog question

11. URGENT URGENT dlopen() help needed...

12. A beginner question - Urgent need help.

 

 
Powered by phpBB® Forum Software