dkim18
asked on
Trigger not working on Sql 2008 server database from my laptop?
Hi,
There are Entered_date and Enetered_by fields on each of tables.
The production database is on the network server. Those two fields are populated fine.
But when I test my application against a test database server on my laptop.
Two fields are not populated.
Two fields are populated by a trigger from this:
ALTER TRIGGER [dbo].[AuditIns_tblxxxx]
ON [dbo].[tblxxxx]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
update [dbo].[tblxxx]
set Entered_date = current_timestamp, Entered_By = system_user
from inserted i
INNER JOIN [dbo].[tblxxx] t on i.[IdGuid] = t.[IdGuid];
END
So it looks like this line is not working when I run my sql server on my laptop.
set Entered_date = current_timestamp, Entered_By = system_user
Any suggestion as why it is not working?
There are Entered_date and Enetered_by fields on each of tables.
The production database is on the network server. Those two fields are populated fine.
But when I test my application against a test database server on my laptop.
Two fields are not populated.
Two fields are populated by a trigger from this:
ALTER TRIGGER [dbo].[AuditIns_tblxxxx]
ON [dbo].[tblxxxx]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
update [dbo].[tblxxx]
set Entered_date = current_timestamp, Entered_By = system_user
from inserted i
INNER JOIN [dbo].[tblxxx] t on i.[IdGuid] = t.[IdGuid];
END
So it looks like this line is not working when I run my sql server on my laptop.
set Entered_date = current_timestamp, Entered_By = system_user
Any suggestion as why it is not working?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It didn't bring back any records.
Humm... try running the query without the WHERE clause. BTW, has the trigger been created on your test database?
ASKER
A copy of the database was attached to my local sql server so it has the tiggers.
I can see it from the table's trigger property.
I can see it from the table's trigger property.
ASKER
The production sql server brought back records with trigger names.
SELECT name, is_disabled FROM sys.triggers
But not from my local sql server.
SELECT name, is_disabled FROM sys.triggers
But not from my local sql server.
Does SELECT * FROM [YourDatabase].[sys].[trig gers] return any rows?
I'm not sure why that happened, but it seems the solution is to recreate the triggers on the test database.
ASKER
Sorry.
You are right. The tiggers are not there on the local test server.
I am pretty sured I've seen them but not now I don't see them any more.
The my sql server just froze and I had to restart it but I don't see why the trigger would not be there.
All I did was attached the copy of the production database to my local test sql server.
Thanks.
You are right. The tiggers are not there on the local test server.
I am pretty sured I've seen them but not now I don't see them any more.
The my sql server just froze and I had to restart it but I don't see why the trigger would not be there.
All I did was attached the copy of the production database to my local test sql server.
Thanks.
ASKER