I have a DTS using an execute SQL Task which is importing
data into two tables linked by field 1, From previous
help, i thought it best to create a trigger on one table
and then drop the trigger after the command has
completed, rather than having 2 different SQL tasks

ExecuteSQL Task is

CREATE TRIGGER TRIG_INSERTCLIRECORD
ON t1.RegisteredUsersTable
FOR INSERT
AS
SET NOCOUNT ON
INSERT INTO t2.ClientAdminTable
VALUES ( Val1,Val2, ETC )
GO


INSERT INTO t1.RegisteredUsersTable
VALUES ( Val1, Val2 ETC)
SELECT @@rowcount AS RecordsUpdatedCount
DROP TRIGGER TRIG_INSERTCLIRECORD

I need to have the trigger set before doind the insert
query, but I get an incorrect Syntax near GO error

Why not just have 2 * ExecuteSQL tasks?

1 does the parent and 1 does the child.

GO is a batch terminator.

t1 and t2 here you are using as Owners of objects. Are they not actually
databases?

This works happily in Pubs and an ExecuteSQL task

CREATE TRIGGER A ON Authors FOR INSERT
AS
select * from titles
Go
SELECT * FROM authors
GO
DROP TRIGGER A

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"Peter Newman" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Alan.

The two tables are in the same database. If i was to use
two seperate ExecuteSQlTasks, how easy would it be to
rollback if either had failed to insert a record. one
table holds Company data whilst the secont holds Company
Registered user data. Both are referenced with by unique
field. I am using @@rowcount to find out if the record
was Inserted. ie if the 1st ExecuteSQLTask inserts the
record, but ExecuteSQLTask 2 failed, i would need to roll
back on ExecuteTask1..

You join the two together in a transaction

http://tinyurl.com/zmlw

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"Peter Newman" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Cheers Allen