Conditional inserts in trigger

Can I have a trigger that inserts into 1 of 3 different tables based on a
column being inserted on the driving table?
For example, I issue
INSERT INTO dbo.People (SSN, CategoryCode)
VALUES (123456789, 3)
If CategoryCode inserted is 3, 10 or 11 then I need to
INSERT INTO dbo.ClientInfo (PeopleID)
VALUES (inserted.PeopleID)
If CategoryCode inserted is 1 then I need to
INSERT INTO dbo.ApplicantInfo (PeopleID)
VALUES (inserted.PeopleID)
etc.
One point that may or may not be important is that the original PeopleID is
assigned in an existing insert trigger and is a random number.
Thanks.
Davidyes - see BOL for more on CREATE TRIGGER, but e.g.
create trigger yourtrigger on People for insert
as
begin
insert into dbo.ApplicantInfo (PeopleID)
select PeopleID
from inserted
where CategoryCode=1
insert into dob.ClientInfo (PeopleID)
select PeopleID
from inserted
where CategoryCode in (3, 10, 11)
end
David Chase wrote:
> Can I have a trigger that inserts into 1 of 3 different tables based on a
> column being inserted on the driving table?
> For example, I issue
> INSERT INTO dbo.People (SSN, CategoryCode)
> VALUES (123456789, 3)
> If CategoryCode inserted is 3, 10 or 11 then I need to
> INSERT INTO dbo.ClientInfo (PeopleID)
> VALUES (inserted.PeopleID)
> If CategoryCode inserted is 1 then I need to
> INSERT INTO dbo.ApplicantInfo (PeopleID)
> VALUES (inserted.PeopleID)
> etc.
> One point that may or may not be important is that the original PeopleID i
s
> assigned in an existing insert trigger and is a random number.
> Thanks.
> David
>|||That doesn't work. I get an error when it tries to create ClientInfo
because ClientInfo table has referrential integrity rule that requires
matching record in People table. Evidently, ref. integrity check does not
know that People table record exists yet. Below is my trigger code, if that
helps.
CREATE TRIGGER T_People_ITrig ON dbo.People FOR INSERT AS
SET NOCOUNT ON
DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
/* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'PersonID' */
SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
SELECT @.newc = (SELECT PersonID FROM inserted)
UPDATE People SET PersonID = @.randc WHERE PersonID = @.newc
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:uHtw5DHHGHA.1180@.TK2MSFTNGP09.phx.gbl...
> yes - see BOL for more on CREATE TRIGGER, but e.g.
> create trigger yourtrigger on People for insert
> as
> begin
> insert into dbo.ApplicantInfo (PeopleID)
> select PeopleID
> from inserted
> where CategoryCode=1
> insert into dob.ClientInfo (PeopleID)
> select PeopleID
> from inserted
> where CategoryCode in (3, 10, 11)
> end
> David Chase wrote:|||David Chase (dlchase@.lifetimeinc.com) writes:
> That doesn't work. I get an error when it tries to create ClientInfo
> because ClientInfo table has referrential integrity rule that requires
> matching record in People table. Evidently, ref. integrity check does
> not know that People table record exists yet. Below is my trigger code,
> if that helps.
Set up the FK to have UPDATE ON CASCADE.
Or instead of an UPDATE, perform first an INSERT, update the childre,
and then delete the original.

> CREATE TRIGGER T_People_ITrig ON dbo.People FOR INSERT AS
> SET NOCOUNT ON
> DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
> /* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'PersonID' */
> SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
> SELECT @.newc = (SELECT PersonID FROM inserted)
> UPDATE People SET PersonID = @.randc WHERE PersonID = @.newc
Keep in mind that a trigger fires once per statement, and thus inserted
can hold many rows.
A better bet for a random number is probably checksum(newid()).
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.seBooks Online for SQL
Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

concatenation

could you give me a dig out with this concatenation, it's driving me nuts
declare @.ev_bat_desc varchar(50), @.text varchar(100)
select @.ev_bat_desc = 'CQA_cmdmgr_Marc.bat'
-- select 'c:\psexec \\10.2.27.230 -I ' + @.ev_bat_desc + ' ;'
-- emulate
Exec master..xp_cmdshell 'c:\psexec \\10.2.27.230 -I' + @.ev_bat_desc + ' ;'
-- works
Exec master..xp_cmdshell 'c:\psexec \\10.2.27.230 -I CQA_cmdmgr_Marc.bat' ;Check whether this works
Exec master..xp_cmdshell 'c:\psexec \\10.2.27.230 -I @.ev_bat_desc '
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"marcmc" wrote:
> could you give me a dig out with this concatenation, it's driving me nuts
> declare @.ev_bat_desc varchar(50), @.text varchar(100)
> select @.ev_bat_desc = 'CQA_cmdmgr_Marc.bat'
> -- select 'c:\psexec \\10.2.27.230 -I ' + @.ev_bat_desc + ' ;'
> -- emulate
> Exec master..xp_cmdshell 'c:\psexec \\10.2.27.230 -I' + @.ev_bat_desc + ' ;
'
> -- works
> Exec master..xp_cmdshell 'c:\psexec \\10.2.27.230 -I CQA_cmdmgr_Marc.bat' ;[/color
]|||Hi, no it just gives me the parameters/functions list for the psExec app.
If I build up the string into one variable eg
@.text = 'c:\psexec \\10.2.27.230 -I ' + @.ev_bat_desc + ' ;'
Exec master..xp_cmdshell @.text
it will work. It means a lot more arsing around re: parameterisation but it
seems a bit silly that its can't build its string in the Exec Stmt.