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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Triggers

Author  Topic 

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-06-11 : 04:54:19
I have a table TB_Clients. The table has two fields Licence & Name. What I want to do is when a new record (client) is added, to have a trigger a dtsx. The dtsx accepts two parameters sLicence, sName.

can anybody give me any help on how to do this ?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-11 : 04:58:54
CREATE TRIGGER dtsx
ON TB_Client
FOR INSERT
AS
BEGIN
-- Code to insert into another table
-- Ex: INSERT INTO NewClientsLog SELECT Licence, Name FROM INSERTED;
END
GO


--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-11 : 05:00:39
hmm..why trigger a dtsx? why not just write a trigger in t-sql to implement the logic? what is it that dtsx is doing?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-11 : 05:14:26
In any case you can do this though but its not a recommended approach

CREATE TRIGGER dtsx
ON TB_Client
FOR INSERT
AS
BEGIN
DECLARE @PK int,@License varchar(100),@Name varchar(50),@dtsExecCmd varchar(max)

SELECT @PK= MIN(PK)
FROM INSERTED

WHILE @PK IS NOT NULL
BEGIN
SELECT @License = License,@Name = Name
FROM INSERTED
WHERE PK = @PK

SET @dtsExecCmd=''

SET @dtsExecCmd = 'dtexec /F "your dtsx file path here"'
SET @dtsExecCmd = @dtsExecCmd + ' /CONFIGFILE "configuration file path"'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::License].Properties[Value]";"\"' + @License + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::Name].Properties[Value]";"\"' + @Name + '\""'

EXEC @result = master..xp_cmdshell @dtsExecCmd
IF (@result is not null and @result<>0)
--write any error logic here


SELECT @PK= MIN(PK)
FROM INSERTED
WHERE PK > @PK
END
END
GO


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-06-11 : 06:06:21
Cheers Guys,

It works a treat. The reason for the need of a dtsx, is to update our development CRM system.

once again cheer guys
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-11 : 06:46:23
ok...you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -