MS SQL Trigger Help

[mattster]

I have a DB with say 5 tables, what i wish to do is when a record is added to Table1 - which uses the auto incriment on the ID - i need to create records in the other 4 tables using that ID from Table1.

In other words when a record is added to Table1, i need new records (just one per table) automatically created in Table2, Table3, Table4 & Table5 - the only data that needs to be inserted into these new records is the ID from Table1.

Unfortunately i am not too familiar with Triggers but i am hoping it can be done using them. Also, i do not know if it can be done simply by creating the trigger on Table1 or whether i would have to create one on Table1 that points to Table2, then Table2 to Table3 etc...

I am using MS SQL 2000 and i only need the trigger(s) to fire when a new record is created in Table1 - not when updated.

Any help with this would be greatly appreciated, I would like some syntax help/example as well if possible.

Many Thanks!

[chazzy]

I'm not overly familiar with SQL Server, but this should help you somewhat:


CREATE TRIGGER TRIGGER_NAME_HERE

ON YOUR_TABLE_HERE

FOR INSERT

AS

--- put all of your insert statements here.

[mattster]

Yeah thanks i had kinda got that far. I have:

CREATE TRIGGER tr_Add_ID_to_tbl2 ON tbl1
FOR INSERT
AS
DECLARE @ID INT
SELECT @ID = (SELECT ID FROM Inserted)
INSERT tbl2 values (@ID)

But it does not seem to like this.

How do i make it so that it simply puts the @ID into the ID field in tbl2? As i see it it does not know which field to put the @ID into... :confused:

Or do i have to insert values into every field in the order they are set?

Thanks.

[mattster]

OK thanks - i managed to figure it out, i had a typo! :mad:

Cheers!

[chazzy]

hmm MS SQL triggers must be setup different - typically in Oracle we get the inserted id as :new.ID

[mencho]

Hi, I print an example on MSSQL:

CREATE TABLE dbo.pruebaSole
(
campo1 numeric(14,0) NOT NULL,
campo2 varchar(10) NULL,
campo3 varchar(10) NULL,
campo4 datetime NOT NULL
)
go

then

CREATE TRIGGER dbo.ControlInsert_I
ON dbo.pruebaSole
FOR INSERT AS
BEGIN
DECLARE NameCursor cursor for
SELECT
campo1,
campo2,
campo3,
campo4
FROM inserted

DECLARE
@campo1 numeric(14),
@campo2 varchar(10),
@campo3 varchar(10),
@campo4 datetime

DECLARE
@Auxcampo1 numeric(14),
@Auxcampo2 varchar(10),
@Auxcampo3 varchar(10),
@Auxcampo4 datetime

DECLARE
@CursorControl numeric (10)

Open NameCursor
FETCH NameCursor INTO @campo1, @campo2, @campo3, @campo4
select @CursorControl = (Select @@Fetch_status)

WHILE @CursorControl = 0
BEGIN
Select @AuxCampo1 = (Select @Campo1)
Select @AuxCampo2 = (Select @Campo2)
Select @AuxCampo3 = (Select @Campo3)
Select @AuxCampo4 = (Select @Campo4)

FETCH NameCursor INTO @campo1, @campo2, @campo3, @campo4
select @CursorControl = (Select @@Fetch_status)

IF @Auxcampo1 = 1
BEGIN
RAISERROR('Incorrect Value', 1, 1)
ROLLBACK TRANSACTION
END

END
CLOSE NameCursor
DEALLOCATE NameCursor
END

then testing

Test Insert Fail:
insert into dbo.pruebasole values (1,2,3,getdate())

Test Insert Ok:
insert into dbo.pruebasole values (2,2,3,getdate())

bye