SQL Server 2008 :: Why Does Backup Cause Trigger To Fire
Mar 2, 2015
A job runs every morning at 3.00 am to back up a database. Many of the tables have triggers on to write updates, deletes and inserts to audit tables.A typical trigger looks like this.
USE [ProjectDB_Live]
GO
/****** Object: Trigger [dbo].[trgStakeHolders] Script Date: 03/02/2015 10:23:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[code]....
I added a trigger to a table over the weekend - structured the same as the one above and, when the back up ran last night, it copied every row in the table into the audit table as if every row in the table had been updated.
View 4 Replies
ADVERTISEMENT
Sep 9, 2015
I have two different SQL 2008 servers, I don't have permission to create a linked server in any of them. i created a trigger on server1.table1 to insert the same record to the remote server server2.table1 using OPENROWSET
i created a stored procedure to insert this record, and i have no issue when i execute the stored procedure. it insert the recored into the remote server.
The problem is when i call the stored procedure from trigger, i get an error message.
Stored Procedure:
USE [DB1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ....
When i try to insert a new description value in the table i got the following error message:
No row was updated
the data in row 1 was not committed
Error source .Net SqlClient Data provider.
Error Message: the operation could not be performed because OLE DB
provider "SQLNCLI10" for linked server "(null)" returned message "The partner transaction manager has disabled its support for remote/network transaction.".
correct the errors entry or press ESC to cancel the change(s).
View 9 Replies
View Related
Nov 15, 2005
Is there a way to fire a trigger when a particular user
(with sql login) logs into sql server?
We are planning to use the above trigger to start a job which inturn records some inf.
Thanks.
View 1 Replies
View Related
Sep 21, 2005
Is it possible to create a trigger to monitor when a value changes from 1 to 0 in a particular row.
Example, rows have names, pins and status columns. When the status column of a row changes from 1 to 0, I am going to send a mail item to the person whose name has had the status changed.
Mike
View 1 Replies
View Related
Mar 10, 2007
I am using SQL Server 2005 database for a webbased application built on .net 2.0. Here is my situation. I have a SubmitDate and a Status field in a table. I want the trigger to fire based on the datevalue in the SubmitDate field. SubmitDate field is the last date for the user to submit an application. For eg: if the SubmitDate value is 03/10/07 , I want the trigger to fire on :01 of 03/11/07 to change the Status field value to Inactive. Is that possible? Thoughts on how to do it?
View 1 Replies
View Related
May 21, 2004
This trigger fired once ONLY during an insert event. My web form inserted 4 news records so it should fire the trigger 4 times correct?
CREATE TRIGGER trInsertImplementationTaskP1
on dbo.ImplementationTasks
FOR INSERT
AS
DECLARE @ITIDint
SELECT @ITID = (SELECT ITid
from inserted)
EXEC TrigSendNewIMAlertP1 @ITID
View 4 Replies
View Related
Oct 10, 2001
Is this possible?
View 1 Replies
View Related
Jan 23, 2004
i want to create a trigger that fire after a ady or
fire 4 pm daily
in SQLSERVER
View 4 Replies
View Related
Oct 29, 2014
Is there a way to make a sql table trigger fire off conditionally on calling app?We have this vendor app database which also have in-house custom built app operates on them. Some one are thinking using triggers to audit second part of the app activities, but want to skip the vendor app part of operation for at least performance reason.To my knowledge, the answer is no. An insert trigger will fire off whenever there is an insert. It doesn't provide a mechanism to check which app/process first, then conditionally fire or not fire.
View 6 Replies
View Related
Feb 1, 2006
I have a trigger on UPDATE on a table. I'm running some maintenanceUPDATE and DELETE queries which I want the trigger to ignore but at thesame time I want other UPDATE queries that other users might be runningto keep triggering the trigger. Is there a SET statement perhaps thatI could set before my query? Or a clause in the UPDATE statement?This is on MSSQL 2000 server, on Win2k3
View 2 Replies
View Related
Jun 1, 2015
IF OBJECT_ID('dbo.AuditLog','U') IS NULL BEGIN
CREATE TABLE [dbo].[AuditLog]
[code]....
[AuditLogId] [bigint] IDENTITY(1,1) NOT NULL,
[DateCreated] [datetime] NOT NULL,
[TableName] [nvarchar](200) NULL,
i have the Audit_log table and the trigger above on Account table..it is working fine and i have some 1000 records in Audit log table , but it did not fire for some customers and i found trigger did not fire for 60 plus customers any possible reasons ??? that trigger did not fire ? Below are the timings account have been created but trigger did not log them
2015-04-29 10:22:08.243
2015-04-29 11:01:49.150
2015-04-29 11:08:16.617
2015-04-29 11:40:34.407
[code]...
View 17 Replies
View Related
Mar 28, 2006
I have tables that I want to fire either an update or insert trigger on.I could write a script containing a long list of inserts but I'm looking forsomething simpler. Would isql work? Any special conditions to get it towork?I've tried tricks like 'update x set col = col' or 'update x set col = col +'' 'All the alternatives seem to have problems. Any ideas?----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups----= East and West-Coast Server Farms - Total Privacy via Encryption =----
View 5 Replies
View Related
Oct 19, 2006
Does anyone know how to do the following. I'm trying to mimicreplication with triggers.I have 2 databases, each have these 2 tables.1.USERSID intNAME varchar(20)2.CHANGESTABLE varchar(20)TYPE varchar(10)col1 varchar(20)col2 varchar(20)On the USERS table I have a for insert trigger. Whenever a new user isadded the trigger puts an entry into the CHANGES table such as("USERS", "INSERT", "1", "Fred")I now have an application (vb.net) that monitors the CHANGES table onserver1. If it finds an entry it determines the table using the TABLEcolumn and performs the necessary insert and deletes the entry fromCHANGES. Now the problem is server2 also has an for insert trigger onthe USERS table so it puts an entry into CHANGES on server2. As youcan imagine this goes around in a loop.What I was hoping for was someway of saying, "I'm inserting from myapplication so don't do the trigger".Any ideas gratefully appreciated.Steve.
View 9 Replies
View Related
Feb 28, 2007
Hi.I need to make a trigger which fires when after an UPDATE is done ontable A in database X. The trigger must perform a statement whichcopies some of the data from the update to table B in database Y onthe same server.My question is: Can I access the data which caused the trigger to fireor just get an indication of which entry in table A had been updated?Best RegardsJens Christian Andersen.
View 2 Replies
View Related
Jun 15, 2004
I've gotten conflicting info about this in the past so I thought I'd try to get clarification.
When a record is deleted, I'm sure it fires the delete trigger. Does it also fire the update trigger?
Thanks
View 3 Replies
View Related
Nov 4, 2015
I created a view that joins several tables together and added an instead of insert, update trigger on it. My trigger works when I do an update/insert directly to the view, but it doesn't seem to fire when an insert/update is made to any of the tables that the view is based on. Is there a way to have the trigger fire when something happens to the tables that the view is based on? The application that the database is using is something that was purchased so I don't have access to the code to change any SQL statements that it's using.
View 5 Replies
View Related
Feb 18, 2015
SQL Server 2008 r2 - 6 GB memory...I attempted a backup on a 500GB database but it was taking way too long. I checked the resources on the box and saw the CPU at 100%. I checked the SQL Server activity log and saw a hung query (user was not even logged on) that had multiple threads so I killed it and now the CPU utilization is back to normal.
Trouble is, now all of the threads in the activity monitor for the backup show 'suspended' and the backup appears to be not doing anything.
View 3 Replies
View Related
Feb 13, 2008
Hi all,
I am sure someone must have run into this before. I have a couple of tables with a parent child relationship.
I created a trigger on the insert of the parent but don't want it to fire until both the parent and child have been inserted into.
However sometimes the child may not get inserted in to at all. In other words it is a 1 to 0 or more relationship.
I created the whole insert into the parent and the child and wrapped it all up in a transaction hoping that the trigger would not fire until the transaction actually completed.
However such is not the case and it fires when the parent is inserted into but nothing is inserted into the child yet even though that is part of the transaction.
Is it possible to postpone trigger fire until after both parent and child table values have been inserted?
Thank you,
John
View 8 Replies
View Related
Mar 13, 2015
I've written a custom script to delete backup files from location. But unable to modify now to count the number of files are deleted. How to modify the script...
/* Script to delete older than N days backup from a specific directory */
USE [db_admin]
GO
IF OBJECT_ID('usp_DeleteBackup', 'P') IS NOT NULL
DROP PROC usp_DeleteBackup
GO
[Code] .....
View 2 Replies
View Related
Mar 11, 2015
I have a view made up of a few base tables and another view. I have created an INSTEAD OF trigger on this view, but it doesn't seem to fire whenever a new record shows in the view. The purpose of the trigger is to insert a sister record in a table whenever a new record shown in the view. Here's the catch, the table that the trigger is supposed to insert into is not a base table within the view and the view is not an updatable view. My question is... Do INSTEAD OF triggers only affect the base table(s) within the view and does the view itself have to be an updatable view?
View 3 Replies
View Related
Jul 23, 2015
Can I accomplish encrypting a column with a trigger similar to below, but without the 'SSN' column?
I added the SSN column just so I could pass an UNencrypted value. Seems round-about & clunky.
This works:
Insert into TestSSNs(UserID, SSN, Name) VALUES (NEWID(), '890-12-0007', 'Gina')
---------------------------------------------
CREATE TABLE TestSSNs (
UserIDuniqueidentifier,
SSNVarchar(11) , --plain text, should always be null
eSSN VarBinary(128), --encrypted value
[Name]Varchar(50)
);
CREATE TRIGGER dbo.IU_TestSSNs ON dbo.TestSSNs
[code]....
View 3 Replies
View Related
Oct 25, 2015
the disable trigger will be enable again.Because I already disable the trigger on last month, but when I check through the database, it enable again.I understand that, when we restore the database, all the trigger will be enable.How about SQL Cluster? will it enable the trigger??
View 3 Replies
View Related
Jun 5, 2012
I am trying to create a trigger with in a stored procedure. When I execute the stored procedure I am getting the following error :
Msg 2108, Level 15, State 1, Procedure JPDSAD1, Line 1
Cannot create trigger on 'FRIT_MIP003_BOK_BTCH_LG.P62XB1.XB1PDS' as the target is not in the current database.
Here is the code for the stored procedure :
CREATE PROCEDURE [dbo].[InsertTRIGGER](@databaseA varchar(50))
AS
BEGIN
exec ('USE ['+@databaseA+'];')
exec ('CREATE TRIGGER [P62XB1].[JPDSAD1] ON [' + @databaseA + '].[P62XB1].[XB1PDS] ' +
'AFTER DELETE AS ' +
'BEGIN ' +
' INSERT INTO [' + @databaseA + '].[P62XB1].[XL1TDS] SELECT CAST(SYSDATETIME() AS DATETIME2(6)) , ''B'' , ''D'' , IDA_DELETE ' +
' ''0001-01-01 00:00:00.000000'' , '' '' FROM DELETED ' +
'END')
END
View 5 Replies
View Related
Mar 11, 2015
I have created an insert trigger to update an address column with a default when it is null, for different clients using a case statement.
When I import data using the import wizard, the trigger doesn't fire.
Doesn't SQL see an import as an insert?
How can I get this trigger to fire for an import?
View 0 Replies
View Related
Apr 6, 2015
What statement do I use, as part of an insert trigger, to insert xml data from the xml database to a flat file database, to check if a record with a specific ID exists to delete first then insert the changed record, instead of adding the changes or an updated from the original xml database.
What I’m trying to do is take the xml formatted data out of one sql server database and insert the data only in that xml into a another sql database. So I can play with the data.
Problem is if the data in the xml is updated or changed for a specific record on the original xml database then the trigger inserts another copy into the created database (which I don’t want).
This is on SQL Server 2008R2.
View 2 Replies
View Related
Mar 14, 2015
I have two tables. Order table and order detail table.
What I want to do is send notification to a service when an order is created. And I want to include both header and detail of the order.
I can't get working with on insert trigger . How to go around with trigger ?
View 1 Replies
View Related
Apr 10, 2015
I am trying to create a trigger on a table. Let's call it table ABC. Table looks like this:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ABC](
[id] [uniqueidentifier] NOT NULL,
[Code] ....
When someone updates a row on table ABC, I want to insert the original values along with the current date and time getdate() into table ABCD with the current date and time into the updateDate field as defined below:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ABCD](
[id] [uniqueidentifier] NOT NULL,
[Code] .....
The trigger I've currently written looks like this:
/****** Object: Trigger [dbo].[ABC_trigger] Script Date: 4/10/2015 1:32:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[ABC_trigger] ON [dbo].[ABC]
[Code] ...
This trigger works, but it inserts all of the rows every time. My question is how can I get the trigger to just insert the last row updated?
I can't sort by uniqueidentifier in descending as those can be random.
View 9 Replies
View Related
Jan 30, 2015
I have a backup that comes to me nightly in the format of XXXX_YY_MM_DD.bak where the date is incremented each night the previous night backup is deleted when the next days is added so in general . I have only one in that folder. I wanted to setup a restore to run nightly
RESTORE DATABASE [XXXData] FROM DISK = 'C:folderExtractedDataapp_XXX_backup_15_01_28.bak' --location of .bak file
WITH REPLACE
I would like to do something like
RESTORE DATABASE [XXXData] FROM DISK = 'C:folderExtractedDataapp_XXX_backup*.bak' --location of .bak file
WITH REPLACE
While I'm asking.. In case there is an older one left behind(which shouldn't happen) I saw something about "LATESTFULL" but think its a redgate command?
View 5 Replies
View Related
Aug 5, 2015
SQL Transaction replication, specifically SQL backup and restore Transaction replication. So Scenario,
S1 = Primary Server 1
R1 = T - Replication Server 1
R2 = T - Replication Server 2
So we have S1 replicating to R1, and we want to build another subscriber which is R2.
Can I take the Replicated Database from R1, backup it up, then restore it to R2, and create the publication/subscription?
Will that work? if not, is there an easier way to avoid the snapshot? the reason i ask this is because we do have replication snapshot, but takes long. One of my Colleagues stated he tried this, however replication made duplicate rows on R2, which is why he had to use replication snapshot.
View 0 Replies
View Related
Aug 21, 2015
I am trying to locate the mdf,log and bak files but I am not seeing folder (MSSQL10_50.MSSQLSERVER) the only folder I see there are 90,100,110 and Report builder.
I located the properties of one of the databases below right clicking the properties on SSMS, but when I physically go there, I don't find that folder.
C:Program Files (x86)Microsoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATA
I gave myself all the permissions under the security tab fro the folder Microsoft SQL Server but still nothing.
View 9 Replies
View Related
Sep 17, 2015
2008 R2 Instance
SQL Server Logs show that a full backup is occurring on each database at 10:30pm every night.
We have a Maintenance Plan the does a full backup on Friday at 8pm and another Plan that does a differential backup on every other day at 8pm.
There are no other Maintenance Plans or Agent Jobs.
Nothing related shows in the Windows Task Scheduler.
There are no related files that have a 10:30ish time stamp (including no backup files).
Where I should look next to find these backups and/or the script/process that is creating them?
View 5 Replies
View Related
Jul 9, 2015
I am looking to created a trigger that inserts records into a log table to show the stored porcedure that has updated a specific column to a specific value in a specific table
At present I have:
CREATE TRIGGER [dbo].[trUpdaterTable]
ON [dbo].[t_account]-- A DB level trigger
FOR UPDATE
--Event we want to capture
AS
IF update (document_status)
[Code] ...
However this doesn't appear to bring back the procedure that triggered the update...
The value the trigger should update on is document_status = 0
DDLProcExecutedByEventDate
NULLNULLLOMBDAadministrator2015-06-25 07:42:01.677
NULLNULLLOMBDA im64602015-06-25 07:51:34.503
NULLNULLLOMBDAadministrator2015-06-25 07:52:01.610
NULLNULLLOMBDAadministrator2015-06-25 08:02:01.417
CREATE TRIGGER [dbo].[trTableupdateaccount] ON [DoesMore].[dbo].[t_account]
[Code] ....
View 9 Replies
View Related
Nov 3, 2010
We use Netbackup for our SQL servers to backup and restore databases. I would like the service account used by Netbackup to have as limited permissions as possible. The account should be able to backup and restore a db without being able to read any of the content. Right now the account jobs fail if the service account is not in the sysadmin role.
I removed the account from sysadmin and limited it to dbcreator and public but the job fail.
How to setup an account so that people who know the service account password can't log in with that account and read db information?
View 9 Replies
View Related
