Insert Trigger sometimes not firing

hi all

i have an issue with an insert trigger sometimes not firing.

here is the trigger

CREATE TRIGGER Insert_tPABillToAddr ON [dbo].[tPA00175]
FOR INSERT
AS

INSERT into tPABillToAddr
(
chrJobNumber
)

SELECT chrJobNumber
FROM inserted

when the user enters a new this table is to insert one column into another table. the thing is, sometimes it does not do the insert. any ideas as to why? it is a very uncommon thing, lets say once out of every 20 inserts does it fail. but it is crucial that it never fails.

thanksYou can be sure the trigger is firing.
Are you sure the insert is occurring? Add a few more lines to the trigger so that data is also inserted into a log table indicating the datetime, login, and chrJobNumber of each insert.
Keep in mind that your trigger will not fire for updates that may modify an existing chrJobNumber value.|||what would be a probable cause for it not to fire? this is just an insert...i have no updates associated with it.|||While I've investigated a few cases where people thought that triggers didn't fire, I've only seen one or two cases in SQL 4.2 and SQL 6.0 where there were actually cases where a trigger should have fired, but didn't. Those were bugs in the database engine itself.

I haven't seen that happen since the release of SQL 6.5.

-PatP|||what if you have more than 1 insert trigger...lets say the other, for whatever reason doesnt fire, does it cause this one to cease as well?

Insert Trigger How to work

Hi All!

I have a table which has fields |person_id| AND |fullname|

I would like to create and insert trigger so that no duplicate names shoudl be inserted, I tryed Following but am geeting confused Where I am going Wrong

I need to take an Input from user to this trigger as well how is it possiible??

create trigger ins_per
on persons
for insert as
Declare @.p_name varchar(45)
select * from persons where full_name=@.p_name
if @.@.rowcount>0
Begin
Print
'Sorry'
ROLLBACK TRANSACTION
EndA unique constraint does the trick!

Alter table x add constraint constraint_name unique nonclustered (Column 1,Column 2)

The combination column1 + column2 must be unique.

Hope it helps!sql

Insert trigger changing record

Hello all!
I want to create an insert trigger to change some fields of the inserted
record. I want to put in two fields the system date and system time.
When I try to update Inserte table I get an error telling me I cannot
update inserted tables.
Can anyone give me a hand on this?
CREATE TRIGGER [Transactions_Insert] ON [dbo].[Transactions]
FOR INSERT
AS
update Inserted
set CreationDate = dbo.idlog_date(current_timestamp)
--
Function idlog_date returns the date in my format.
Thanks in advance,
Hugo MadureiraHugo Madureira wrote:
> Hello all!
> I want to create an insert trigger to change some fields of the inserted
> record. I want to put in two fields the system date and system time.
> When I try to update Inserte table I get an error telling me I cannot
> update inserted tables.
> Can anyone give me a hand on this?
>
CREATE TRIGGER [Transactions_Insert] ON [dbo].[Transactions]
FOR INSERT
AS
UPDATE dbo.transactions
SET CreationDate = dbo.idlog_date(current_timestamp)
GO
It seems like overkill to use a trigger for this. Have you considered
declaring a DEFAULT value instead: DEFAULT CURRENT_TIMESTAMP.

> Function idlog_date returns the date in my format.
A DATETIME column doesn't have a "format". Why store the date as
anything other than DATETIME or SMALLDATETIME?
David Portas
SQL Server MVP
--|||Yes, thats right. You have to update the original data which is already
store in there.
UPDATE Transactions
SET CreationDate = dbo.idlog_date(current_timestamp)
FROM Transactions T
INNER JOIN INSERTED I
ON T.<YourprimaryKey> = I.<YourprimaryKey>
HTH, Jens Suessmeyer

insert statement problem

Dear all
I have two db table

Product (ProdID, ProdName,....Condition1)
ProdSellRec (ProdDetailID, ProductDesc,......ProductID)

When i would like to create a insert statement into second table!

insert into ProdSellRec
( @.ProductDesc,......@.ProductID)
where @.ProductID = (select ProductID from Product where ......[some condition]..)

Howver , the db want me "Unable to parse query text."/

What should I change my sql statement?

Thanks!

Ad_dee

it might be easier if you show your whole sql statement. It is hard to tell from that
|||

Thanks , i will post the sql later

But I would lile know whether the method of nested query is corrected in my sql.

Insert Statement Help

hi all

i am writing a trigger that inserts from one table to the next. i have an issue with the table being inserted into have 2 more columns than the one being inserted from. Here is the trigger just in case

CREATE TRIGGER [Insert40801] ON [dbo].[tPA10801]
FOR INSERT

AS

insert into tPA40801
select
intTimesheetKey,
chrTimesheetNumber,
TranID,
intEmployeeKey,
intEmployeeDivKey,
BatchKey,
dtePeriodStartDate,
dtePeriodEndDate,
chrStatus1,
numTotalHrsWorked,
numTotalHrsBilled,
numTotalCosts,
numTotalCharges,
numTotalRecover,
chrSignedID,
dteSignedDate,
chrApprovalID,
dteApprovalDate,
intJobKey,
intPhaseKey,
intTaskKey,
dteDate,
siCstClsificatnDDL,
UpdateCounter,
CompanyID


from tpa10801

now tpa40801 has two extra columns that tpa10801 doesnt. how would i please the sql gods and get the insert statement running? thanks alot

tiborYou're missing a few of the items that are suggested in the FAQ Entry (http://www.dbforums.com/showthread.php?t=1212452#post4527530) that would probably get you the answer you need on your next try. Specifically, it would help me a lot to know what the table structures are now, so I'd know which columns were missing.

-PatP|||actually i was able to overcome that issue but just deleting the columns from tPA40801. but now i have an issue with the batchkey column. the program that this is all for is recognizing that it is a batchkey and does not allow the program to save while this is trying to insert. so i just need to be able to insert everything there except the batchkey column. hope that helps

Insert rows from other server

Hi All

I want to insert rows from a table in a server
into another table in another server using
INSERT SELECT command. For example :

INSERT INTO Server1.database1.dbo.Tab1
SELECT * FROM Server2.database2.dbo.Tab1
WHERE Col1 = 1

Can the command like this work ?
If not, could you give me the solution ?

Please help me

Thanks in advance

John Smile

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!You have the correct syntax. Assuming you have created a linked server on
the server where you run the code and provided you have the required
permissions then your INSERT statement looks OK. See the Linked Servers
topic in Books Online for details.

BUT, don't use SELECT * in production code and always list the target column
names in an INSERT statement. Following those good practices should make
your code easier to maintain if your table structures change.

--
David Portas
SQL Server MVP
--|||Hi David,

Thank you for your reply.

Currently I have 2 servers that work as domain
controller and member server. Both of them run
SQL Server. My command works on the member server
and does not work on domain controller (DC).
The DC can not see the member server
and clients. It means when I open a client path
in my network places (using server windows explorer),
I get an error (\\Client is not accessible).
But the member server and clients can see the DC
and they also able to see them each other.

I think there is something wrong in my windows
setting that causes the command does not work on
the DC. If you do not mind, could you show me
what setting that cause this problem ?

John Smile

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hi David,

Thank you for your reply.

Currently I have 2 servers that work as domain
controller and member server. Both of them run
SQL Server. My command works on the member server
and does not work on domain controller (DC).
The DC can not see the member server
and clients. It means when I open a client path
in my network places (using server windows explorer),
I get an error (\\Client is not accessible).
But the member server and clients can see the DC
and they also able to see them each other.

I think there is something wrong in my windows
setting that causes the command does not work on
the DC. If you do not mind, could you show me
what setting that cause this problem ?

John Smile

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Insert rows from other server

Hi All

I want to insert rows from a table in a server
into another table in another server using
INSERT SELECT command. For example :

INSERT INTO Server1.database1.dbo.Tab1
SELECT * FROM Server2.database2.dbo.Tab1
WHERE Col1 = 1

Can the command like this work ?
If not, could you give me the solution ?

Please help me

Thanks in advance

John Smile

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!You have the correct syntax. Assuming you have created a linked server on
the server where you run the code and provided you have the required
permissions then your INSERT statement looks OK. See the Linked Servers
topic in Books Online for details.

BUT, don't use SELECT * in production code and always list the target column
names in an INSERT statement. Following those good practices should make
your code easier to maintain if your table structures change.

--
David Portas
SQL Server MVP
--

Insert procedure from Excel

Hi all

I need to create a stored procedure that will insert from an Excel document. And I'm not exaclty sure of how to do that.

CREATE PROCEDURE [Insert_ActiveSuspensions]

AS
INSERT INTO [GamingCommissiondb].[dbo].[License_SuspensionsView]
([TM #],
[FIRSTNAME],
[LASTNAME],
[SS #],
[REASONFORSUSPENSION],
[ENDDATE]
[BEGINDATE])


SELECT
[TM#],
[LASTNAME],
[FIRSTNAME],
[SSN#],
[NOTES],
[DATEOFCONDITIONAL]

FROM "C:\Documents and Settings\Desiree Stevenson\My Documents\TerminationInserts.xls"
IF @.@.Error <> '0'

is this correct??create a linked server to the excel file. look up sp_addlinkedserver in BOL.|||I am wanting to export from a table to an Excel spreadsheet and then to a table in another database.

I should explained that better sorry|||Consider the import/export wizard in Enterprise Manager (SQL 2000) or Management Studio (SQL 2005).|||Try with BCP command......... Google "BCP"......... There is your answer.......

Thanks,
Rahul Jha|||With Excel, make sure SET NOCOUNT ON is specified in the procedure. It causes problems called by an xla.|||Try with BCP command......... Google "BCP"......... There is your answer.......

Thanks,
Rahul Jha

excuse me?

this message...|||I am wanting to export from a table to an Excel spreadsheet and then to a table in another database.

I should explained that better sorry

ummm, why?

is the database on the same server/instance?