1
vote
0answers
25 views

How To Log Select Statements from 1 User On 1 Table Into a Permanent Log Table

We have a centralized authentication library that 100+ applications use to authenticate users. That library has its own DB credentials. It would be extremely useful to log every time that library/user ...
0
votes
0answers
24 views

Service Broker received an error message on this conversation

I will first have to say, I am still trying to wrap my brain around all the components of the Service Broker and how they work together, so it's very likely I'm missing something obvious... it's just ...
0
votes
3answers
49 views

Instead of Trigger Continue with operation after custom logic?

I do not think what I am trying to do is possible, but I could not find any documentation on this. I have an INSTEAD OF UPDATE, INSERT, DELETE that always inserts data into a history table before ...
2
votes
1answer
49 views

How to resolve logon trigger and EXCEPTION_ACCESS_VIOLATION?

My production sql server is running... Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Sep 18 2014 16:17:31 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows ...
0
votes
2answers
64 views

Send email when some users connect to SQL Server

I am looking for a way to create a logon trigger in SQL Server. Basically i need to do is when it matches some users or profiles on logon it send an alert e-mail. I have found some things for Oracle ...
0
votes
2answers
44 views

Triggers not reflecting on procedure

I have a trigger create trigger flightinput2 on reservation after insert as begin update reservation set Departure_Airport='LDN', Arrival_Airport='KUL' where Flight_Code='EM0088' end ...
0
votes
1answer
23 views

Getting the value from a column during a trigger [closed]

After a test completes (on a SQL 2008R2 system), the system updates a row in the TestRun table to include an end time. I am creating a trigger that will take action after that update, and do some ...
0
votes
1answer
75 views

sql expiry date use Trigger OR Schedule a Job

I have this table : Now, i like the way when the date expires. EndTime < GETDATE() expire field is true. What method should I use? use a trigger to true or a schedule job that run before run ...
0
votes
1answer
36 views

Trigger on a Schema

Is it possible to create a Trigger on a Schema? I have to audit the changes on my tables. But I have a lot of tables and I don't one to create a trigger on them one by one. --Database level trigger. ...
2
votes
2answers
129 views

how to create backup for stored procedure,trigger and functions of a database in sql server 2008?

I'm having 2 databases in 2 different servers(.246 and .252). I need to create backup for all stored procedures, triggers and function from 252 server and restore it in 246 server.database name in ...
1
vote
1answer
85 views

How to support multiple updates in this trigger?

Below is the code of a trigger, basically it calls a function in case the record that is being updated does not have other row associated by it's Id in another table. In a multiple row update this ...
1
vote
1answer
265 views

Does the Inserted table contain Identity values?

I have an issue with this AFTER INSERT trigger that I'm using for auditing: Cannot insert the value NULL into column 'TermID', table 'AuditTerms'; column does not allow nulls. INSERT fails. ...
1
vote
1answer
1k views

Trigger to audit table changes

I have created a trigger to audit changes to a table: CREATE TRIGGER [dbo].[iudt_AutoAuditChanges] ON dbo.CPTCategoryMaster AFTER INSERT,DELETE,UPDATE AS BEGIN SET NOCOUNT ON; ...
4
votes
1answer
171 views

What is the correct way to ensure unique entries in a temporal database design?

I'm having trouble with the design of a temporal database. I need to know how to make sure I have only one active record for any given timeframe for a store. I have read this answer, but I'm afraid I ...
0
votes
1answer
2k views

Trigger to update a date field on one table when another table is updated

I want to be able to update a date filed in one table when another table is updated. I have the following two tables: Transdetails, Customers. In the Customers table I have a field called ...
7
votes
1answer
480 views

Synchronization using triggers

I have a requirement similar to previous discussions at: Writing a simple bank schema: How should I keep my balances in sync with their transaction history? Trigger in combination with transaction ...
4
votes
1answer
103 views

Is it possible to return a custom error for a logon trigger?

I'm trying to get a logon trigger to return a custom error. Thus far everything I have tried causes it to return the standard error: My trigger looks like this: CREATE TRIGGER Test_Logon_Trigger ...
3
votes
1answer
83 views

Can't update trigger because of activity

Trying to add a line to a trigger that is attached to a table that is constantly in use ALTER TRIGGER [dbo].[name_of_trigger] ON [dbo].[name_of_table] AFTER UPDATE BEGIN IF ORIGINAL_LOGIN() in ...
2
votes
0answers
90 views

logon trigger to send an email

I want to create a simple login trigger. basically if user = test , then send an email to me. CREATE TRIGGER ServerLogon ON ALL SERVER WITH EXECUTE AS 'test' FOR LOGON AS BEGIN IF ...
2
votes
0answers
42 views

Setting up triggers based on exposed columns in a view's source table

I have a table where I want to do a AFTER UPDATE trigger if one of the columns modified was in a specific view CREATE VIEW [dbo].[iv_EmployeesView] AS SELECT EMPLOYEE_GUID AS EmployeeGuid, ...
1
vote
1answer
79 views

Trigger to restrict users from creating a new table in the database

How do I create a SQL Server trigger that will restrict any users from creating a new table in the database? Please help me with this. Thanks & Regards, Ritesh.
1
vote
1answer
246 views

Is it possible to raiserror within a For Delete Trigger that rolls back the deletes

There is a claim at the end of this accepted answer that if an error occurs within a "For Delete" trigger, the delete action would be rolled back because it is part of an implicit transaction. ...
1
vote
1answer
119 views

how to prevent dropping sysadmin user by another user in sql server

I have a legacy application installed on my server which has configured to work only with one user "sa" and there is time bomb like script written inside this application which whenever it finds ...
0
votes
0answers
41 views

Should triggers be used as an extra precaution to enforce business rules? [closed]

I'm new to SQL Server, and I was wondering: how often do DBA's use triggers to enforce business logic? As long as your security is tight and no user has db_datawriter (or some other all-writing ...
0
votes
1answer
151 views

Trigger After Insert is preventing insertion

I have two tables that must have similar information, and one of them can be considered the base table for to other, so in order to have the info in the two tables, I create this trigger: ALTER ...
0
votes
0answers
104 views

Minimizing possible trigger impact

I'm trying to make sure a trigger doesn't cause trouble. (Quixotic, I know!) Is there more I can do to the trigger below to make it bullet-proof? Details: We have a huge table we don't control ...
0
votes
2answers
281 views

SQL Server 2012. Update second table based on input from first table

I have two tables in two databases: Table01 in Database01 and Table02 in Database02. Both tables have a column called UserID. The UserID column is created when the user creates a new account on the ...
0
votes
1answer
144 views

How to create a trigger that update the date and time field and modify of the row

I want to create trigger that will update the record. The situation is like: I have one column named CheckInDateTime and one more named Status. If the administrator is not able to update the Status ...
3
votes
3answers
1k views

How to know the user doing a delete action on an audit table, when using a shared login?

Background information: I'm creating a collection of audit tables to keep track of updates and deletes to a set of data tables for my app. Audit records are created via triggers. DML in my app's ...
2
votes
2answers
1k views

Table data transfer using SSIS and triggers on destination table

I'm a novoice in db operations. SQL Server 2008 R2 is the database I'm using. I need to move selected rows from a staging database table to production database table. I'm using SSIS package to do ...
1
vote
1answer
530 views

Server Wide DDL Trigger Permissions Issue

I am trying to implement a server wide DDL Trigger that will audit the DDL changes across the databases on the server using script very similar to the article found here The trigger will log the DDL ...
1
vote
1answer
245 views

Slow query and Insert with trigger

I have vehicle tracking system application based on PHP, SQL Server 2008 R2 Enterprise Edition with XEON HP Z800 Server. My database is growing day by day in millions and it makes it very slow to ...
5
votes
1answer
256 views

How to get output from trigger on linked server

Fair warning, I know this isn't the best way to do this, but I'm trying to extend some existing code without needing to do a rewrite. Anyway, I've got two SQL Server 2012 installs, one local in my ...
2
votes
2answers
2k views

After update triggers that deal with multi-row updates

Currently working on an database Audit project based on triggers that are fired on update on specific tables. The triggers write the changes into a table; information written are: table name, updated ...
3
votes
4answers
1k views

SQL Server audit query that fires a trigger

Using SQL Server, does exist a way to audit from inside a trigger the sql that fires it? I need to know the SQL query that fires a trigger over a database without a profiler. Thanks
-3
votes
3answers
95 views

Trigger for database [closed]

As far as I understand Trigger can be written for the table of the database. Now I want the trigger for database. The database can contain multiple table, If there is any change even in one table the ...
1
vote
1answer
489 views

Capture Data Change Vs Triggers [closed]

I am constructing a database that needs to keep track of historical data. It is a Electronic medical system. All data needs to be updated in real time so the user can see what changes happened in the ...
3
votes
1answer
537 views

Subquery returned more than 1 value

In my database I have two tables: projects and comp_types. When a new project is created a trigger is hit which inserts a concatenated value in a column in the projects table. A fully working SQL ...
2
votes
1answer
110 views

What are the risks for logging across databases via a trigger?

I've searched for this online and had mixed or unclear responses, and after posting on superuser, was directed over here. On SQL Server 2005, we currently log certain table changes via triggers using ...
1
vote
1answer
459 views

What would cause extra records to be inserted during a INSERT trigger

The following trigger that joins Inserted table on itself creates multiple unwanted records: CREATE TRIGGER [dbo].[trFooInsert] on [dbo].[Foo] AFTER INSERT AS BEGIN IF EXISTS(SELECT 1 FROM ...
1
vote
2answers
656 views

SQL Server 2012, restore database

I am using MS SQL Management Studio 2012 to restore the database from a .bak file. (That file is from the backup of the database on server) In my new database, the triggers are missing. Why did I ...
2
votes
2answers
788 views

Is there a way that I can export a list of all table triggers on a server?

I have two SQL Server instances that I manage. One is a SQL Server 2000 instance, and the other is 2005. Some where, on these servers, I recall setting up a couple table triggers that were executed ...
10
votes
1answer
4k views

Trigger in combination with transaction

Suppose we have the following situation: We have a table (let's say Table_A), wich has a trigger on INSERT. The trigger job is to update some rows in table_B based on the inserted values in table_A. ...
-1
votes
1answer
324 views

Is it possible to create a trigger on a table synonym in SQL Server? [closed]

The title says (asks) it all. Is it possible to create a trigger on a table synonym in SQL Server (2005 or higher)?
1
vote
2answers
3k views

Multiple triggers vs a single trigger [closed]

Scenario: Each time data is inserted/updated/deleted into/in/from a table, multiple unrelated items of business logic need to be executed. Question Given that the solution is to use database ...
2
votes
1answer
2k views

Msg 7391, Distributed Transactions (DTC) on SQL Server

This may be DBA related or developer related, I honestly don't know. I have two Database servers: A: SQL Server 2005 (Trigger that uses DBMail on Server B) B: SQL Server 2008 R2 (DBMail configured) ...
5
votes
2answers
1k views

considerations when using triggers on a replicated (target) database

Our SQL Sever 2008 application database is replicated from Server A to Server B (push replication). We use the copy, let's call it database_b, on Server B to create reports and run other queries so ...
0
votes
1answer
132 views

How triggers are called when we have two triggers?

In a table I have two triggers. When it is called for the first time the first trigger does not fires while the second trigger's got fired. On the next call both first and second trigger action take ...
3
votes
1answer
266 views

Restrict record to be update or deleted according to dates inside row for SQL Server 2005

I have transaction replication between two SQL Servers. For testing purposes I deleted a lot of records at the subscriber that still existed at the publisher. I now know that if someone updates, ...
4
votes
2answers
2k views

Deadlock on insert for a table with an insert trigger that updates column in same triggered table

By specification each table uses a guid as the primary key. Another requirement is that each resource requires a 6 number identifier (100001, 100002, ...) as a public id that is unique within the ...