variables within triggers

[pvmurphy]

I've taken the code below from this site, it is from a store procedure example. Is there any reason why this would not also work for an INSERT Trigger? I'm trying but I keep getting the message that I must declasre the variable first.

declare @my_var VARCHAR(35)
select @my_var=CUST_NAME_S from CUSTOMER
---PRINT @my_var

thanks

Jim

 

[ClaireHCM]

Can you post out your trigger code?

 

[pvmurphy]

Here's the trigger, wtih actual variables I'm trying to set. My problem is the @commonDB, where I'm trying to get a database name that will be unique to this server. When I try to execute, it tells me I must declare the variable. If I just set it to a string variable, it is fine.

Thnaks

CREATE TRIGGER tgInsPubCat
ON dbo.PPPCat
FOR INSERT
AS
BEGIN
DECLARE @acronym char (3)
DECLARE @Acctno char (8)
DECLARE @dtcat smalldatetime
DECLARE @cdcat varchar (3)
DECLARE @tramount as smallmoney
DECLARE @ccNum varchar (16)
DECLARE @ccexp varchar (6)
DECLARE @batch varchar (4)
DECLARE @checkno varchar (34)
DECLARE @pass varchar (6)
DECLARE @commondb varchar (35)
SELECT @ccexp = cc_exp,
@acronym = acronym,
@Acctno = acctno,
@dtcat = dtcat,
@cdcat = cc_exp,
@tramount = tramount,
@ccNum = checkno,
@ccexp = cc_Exp,
@batch= batch,
@pass = 'PASS'
FROM INSERTED
If Len(@ccexp) > 0
BEGIN
---Get common database, get server path for write
SET @commondb =(SELECT name FROM master..sysdatabases where name like
'[a-z]%Common')
INSERT INTO @commondb VALUES (@acronym, @Acctno, @dtcat, @cdcat,@tramount, @checkno, @ccexp,@batch)

END

END

 

[JayKusch]

Iv'e never seen an INSERT INTO 'Database' command before. Table Yes ... DB No ...

Is that even possible?

Thanks

J. Kusch

 

[JayKusch]

If you are wanting to do that ... you may need to tweak your code in this manner.

CREATE TRIGGER tgInsPubCat
ON dbo.PPPCat
FOR INSERT
AS
BEGIN
DECLARE @acronym char (3)
DECLARE @Acctno char (8)
DECLARE @dtcat smalldatetime
DECLARE @cdcat varchar (3)
DECLARE @tramount as smallmoney
DECLARE @ccNum varchar (16)
DECLARE @ccexp varchar (6)
DECLARE @batch varchar (4)
DECLARE @checkno varchar (34)
DECLARE @pass varchar (6)
DECLARE @commondb varchar (35)
DECLARE @SQLCommand varchar (1000)
SELECT @ccexp = cc_exp,
@acronym = acronym,
@Acctno = acctno,
@dtcat = dtcat,
@cdcat = cc_exp,
@tramount = tramount,
@ccNum = checkno,
@ccexp = cc_Exp,
@batch= batch,
@pass = 'PASS'
FROM INSERTED
If Len(@ccexp) > 0
BEGIN
---Get common database, get server path for write

SET @commondb =(SELECT name FROM master..sysdatabases where name like
'[a-z]%Common')

SET @SQLCommand = 'INSERT INTO ' + @commondb + ' VALUES (@acronym, @Acctno, @dtcat, @cdcat,@tramount, @checkno, @ccexp,@batch)'

EXEC (@SQLCommand)
END

END


Thanks

J. Kusch

 

[pvmurphy]

THanks, I'll give it a try.

 

[pvmurphy]

Jay,

Was able to try your example today, the syntax worked! Thanks a whole lot!

Jim