
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