create a Insert trigger

I need to create a trigger on tblBag_data to add new rows to tblBag_databak, but I'm getting a syntax error.

CREATE TRIGGER trgtblBag_databak ON [tblBag_data]
FOR INSERT
AS

If @.@.trancount > 0
insert tblBag_databak
--return
end

Thanks for your help.Why are you checking @.@.trancount?

What kind of insert is that?

You have an END with no BEGIN

How about...

CREATE TRIGGER trgtblBag_Data ON tblBag_data
FOR INSERT
AS

INSERT INTO tblBag_databak (supply column list here)
SELECT supply column list here
FROM inserted
GO

But why are you keeping duplicate data in 2 tables?

I track updates and deletes, and leave the "base" rows in the INSERT table...|||[QUOTE][SIZE=1]Originally posted by Brett Kaiser
Why are you checking @.@.trancount?

What kind of insert is that?

You have an END with no BEGIN

How about...

CREATE TRIGGER trgtblBag_Data ON tblBag_data
FOR INSERT
AS

INSERT INTO tblBag_databak (supply column list here)
SELECT supply column list here
FROM inserted
GO

Hi,

Thank you so much for your quick reply. It is working.
I need one table to show all the records and another used to be update.

Thanks again.|||Well it's funny...

I try to keep historical data, so I can track activity against a table...

I have on Current table, and 1 historical table...all the updates and deletes are dtored in the historical, and all the current (Inserts, and new updates) are stored in current...

Notice the addition of "administrative" columns to track when the event occured. And don't be to put off by the trigger...I'm using connection pooling and do stuff a little different with User Id's

Anyway, good luck

CREATE TABLE [dbo].[Company] (
[Company_Name] [char] (30) NOT NULL ,
[Active_Ind] [bit] NULL ,
[Psft_Company_Id] [char] (3) NULL ,
[FEIN] [char] (10) NULL ,
[Has_NonQual_Taxes] [bit] NULL ,
[Has_Qual_Taxes] [bit] NULL ,
[Created_By] [char] (8) NOT NULL ,
[Created_Ts] [datetime] NOT NULL ,
[Updated_By] [char] (8) NULL ,
[Updated_Ts] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Company_H] (
[HIST_ADD_TS] [datetime] NOT NULL ,
[HIST_ADD_TYPE] [char] (1) NOT NULL ,
[HIST_ADD_BY] [char] (8) NOT NULL ,
[HIST_ADD_SYSUSER_BY] [char] (8) NOT NULL ,
[Company_Name] [char] (30) NOT NULL ,
[Active_Ind] [bit] NULL ,
[Psft_Company_Id] [char] (3) NULL ,
[FEIN] [char] (10) NULL ,
[Has_NonQual_Taxes] [bit] NULL ,
[Has_Qual_Taxes] [bit] NULL ,
[Created_By] [char] (8) NOT NULL ,
[Created_Ts] [datetime] NOT NULL ,
[Updated_By] [char] (8) NULL ,
[Updated_Ts] [datetime] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Company] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[Company_Name]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[Company] WITH NOCHECK ADD
CONSTRAINT [DF__Company__Created__619B8048] DEFAULT (getdate()) FOR [Created_Ts]
GO

ALTER TABLE [dbo].[Company_H] WITH NOCHECK ADD
CONSTRAINT [DF__Company_H__HIST___59C55456] DEFAULT (getdate()) FOR [HIST_ADD_TS]
GO

CREATE TRIGGER Company_UpdTr ON Company
FOR UPDATE, DELETE
AS

If Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)
BEGIN

Insert Into Company_H (
HIST_ADD_TYPE
,HIST_ADD_BY
,HIST_ADD_SYSUSER_BY
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
)
Select
'U'
,(Select Inserted.Updated_By from Inserted
Where Deleted.Company_Name = Inserted.Company_Name)
,user
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
From Deleted
END

If Not Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)

BEGIN

Insert Into Company_H (
HIST_ADD_TYPE
,HIST_ADD_BY
,HIST_ADD_SYSUSER_BY
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
)
Select
'D'
,user
,user
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
From Deleted
END

Create @tablename gives syntax error

I am trying to create a table named "salesfacts" as follows:
declare @.tname1 as char(30)
set @.tname1 = 'SalesFacts'
CREATE TABLE @.tname1 ([cuid] [char](15)...
and get an error : Incorrect syntax near '@.tname1' ...
The error is related to the variable name - and not to what follows it, i.e.
'([cuid ...', as I could prove.
Only "Create table salesfacts ... " gives no error.
How can I use a variable name here instead of hard-coding the table name?
TIA> How can I use a variable name here instead of hard-coding the table name?
You'll need to build the desired SQL statement as a string and then execute
it dynamically using EXECUTE or sp_executesql. See Erland's thorough
article on the subject: http://www.sommarskog.se/dynamic_sql.html
Hope this helps.
Dan Guzman
SQL Server MVP
"PeterK" <invalid@.verizon.net> wrote in message
news:OZOGuLf0FHA.2064@.TK2MSFTNGP09.phx.gbl...
>I am trying to create a table named "salesfacts" as follows:
> declare @.tname1 as char(30)
> set @.tname1 = 'SalesFacts'
> CREATE TABLE @.tname1 ([cuid] [char](15)...
> and get an error : Incorrect syntax near '@.tname1' ...
> The error is related to the variable name - and not to what follows it,
> i.e. '([cuid ...', as I could prove.
> Only "Create table salesfacts ... " gives no error.
> How can I use a variable name here instead of hard-coding the table name?
> TIA
>
>|||"PeterK" <invalid@.verizon.net> wrote in message
news:OZOGuLf0FHA.2064@.TK2MSFTNGP09.phx.gbl...
>I am trying to create a table named "salesfacts" as follows:
> declare @.tname1 as char(30)
> set @.tname1 = 'SalesFacts'
> CREATE TABLE @.tname1 ([cuid] [char](15)...
> and get an error : Incorrect syntax near '@.tname1' ...
> The error is related to the variable name - and not to what follows it,
> i.e. '([cuid ...', as I could prove.
> Only "Create table salesfacts ... " gives no error.
> How can I use a variable name here instead of hard-coding the table name?
> TIA
>
>
You can't parameterize table names like that. But why would you want to in
this instance?
CREATE TABLE SalesFacts ([cuid] [char](15)...
David Portas
SQL Server MVP
--