0
votes
1answer
14 views

Getting the value from a column during a trigger

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
44 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
28 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
93 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
75 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
152 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
394 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; ...
3
votes
1answer
129 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
998 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
358 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
87 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
71 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
76 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
36 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
68 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
167 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
111 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
39 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
114 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
85 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
202 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
130 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 ...
2
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
423 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
227 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
237 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
399 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
488 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
102 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
402 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
598 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
680 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
3k 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
285 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
2k 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
127 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
254 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 ...
3
votes
1answer
218 views

Error when UPDATE all rows

When I update a single row, it works fine. But when I update all rows with: UPDATE cad_bilhetes SET ligacao_acobrar = 'False' I get the following error: Msg 512, Level 16, State 1, Procedure ...
6
votes
3answers
3k views

Restrict update on certain columns. Only allow stored procedure to update those columns

I have sensitive price columns that I would like to have updated only through a stored procedure. I would like all code or manual attempts to alter values in these price columns to fail if it is not ...
3
votes
1answer
202 views

For creating sum columns in a table that requires immediate reflection, is it better to have persisted columns or triggers?

Is it better to have persisted columns or triggers if you would like to have auto-updating sum/avg/etc... columns? Like if I have this table: CREATE TABLE [dbo].[tbl_shipping_boxes] ( ...
2
votes
1answer
1k views

SQL Server trigger (update or insert with inner join problem)

ALTER TRIGGER [dbo].[killertrigg] ON [dbo].[tbl_pvporderview] AFTER UPDATE AS IF (UPDATE([Kill])) BEGIN SET NOCOUNT ON; INSERT killer(serial,[Kill]) SELECT i.serial,i.[Kill] FROM ...
2
votes
2answers
503 views

Error updating sql server geography type in trigger

I have the following trigger in my database: CREATE TRIGGER dbo.triggerGeocodedAddressUpdate ON dbo.Party AFTER UPDATE AS IF UPDATE(Latitude) UPDATE pt SET pt.GeocodedAddress = ...
2
votes
1answer
1k views

SQL Select WHERE row updated

USE [RF_WORLD] GO /****** Object: Trigger [dbo].[lastdeathtrigg] Script Date: 08/20/2012 10:52:58 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ...