Help Needed For Identifying DML Trigger Event Type
Jun 19, 2008
Hi,
I have a query regarding DML Triggers.
I have created a trigger ON a table AFTER Insert, Update staement. In the trigger's body I am making a call to a stored procedure. In the procedure, I want to know the Trigger invoked due to which event : insert or Update as I have to handle bith the cases differently.
But I am not able to find out any SQL server function or property which can distinguish between INSERT and Update events of a trigger.
I have created a trigger ON a table AFTER Insert, Update staement. In the trigger's body I am making a call to a stored procedure. In the procedure, I want to know the Trigger invoked due to which event : insert or Update as I have to handle bith the cases differently. But I am not able to find out any SQL server function or property which can distinguish between INSERT and Update events of a trigger.
Can anyone offer any advice. I was wondering whether MS SQL server offeredany facilities to trigger an event after a period of time had lapsed? e.g.once a week check data and if it is out of date email an related address.many thanks
Hi There, I have created a trigger which supposingly will do event before delete the record from its own table.unfortunately when i try delete the record to test it whether it will do the event (inserting some records to another table), i found that it was not doing the event like i wanted to be. :(the trigger is as below :=======================CREATE TRIGGER TG_D_AGENT ON dbo.AgentFOR DELETEASbegindeclare @vAgentID as numeric,@vAgency as varchar(50),@vUnit as varchar(50),@vAgentCode as varchar(50),@vName as varchar(50),@vIC as varchar(14),@vAddress as varchar(100),@vContactNumber as varchar(50),@vDownlink as varchar(50),@vGSM as varchar(10),@vAM as varchar(10),@vDeleted_date as datetime set @vDeleted_date = convert(datetime, convert(varchar(10) , getdate(),103),103)declare cur_policy_rec CURSOR forselect AgentID,Agency,Unit,AgentCode,[Name],IC,Address,ContactNumber,Downlink,GSM,AM from insertedopen cur_policy_recfetch from cur_policy_rec into @vAgentID,@vAgency,@vUnit,@vAgentCode,@vName,@vIC, @vAddress,@vContactNumber,@vDownlink,@vGSM,@vAM WHILE @@FETCH_STATUS=0BEGIN INSERT INTO [Agent_history] (AgentID,Agency,Unit,AgentCode,Name,IC,Address,Con tactNumber,Downlink,GSM,AM,Deleted_date) VALUES(@vAgentID,@vAgency,@vUnit,@vAgentCode,@vNam e,@vIC,@vAddress,@vContactNumber,@vDownlink,@vGSM, @vAM,@vDeleted_date)fetch from cur_policy_rec into @vAgentID,@vAgency,@vUnit,@vAgentCode,@vName,@vIC, @vAddress,@vContactNumber,@vDownlink,@vGSM,@vAM enddeallocate cur_policy_recend===============================in oracle , i normallly can do something like this...====================================CREATE TRIGGER TG_D_AGENT ON dbo.AgentBEFORE DELETE ON dbo.Agent FOR EACH ROWbeginIs that such thing function like 'BEFORE' in MS SQL SERVER 2000, coz in sql server im not sure they do have or not. Plz someone help me on this...realy appreciated if can!
Hello. is there an event that exists for server level triggers when a database restore occurs? I don't see restore_database as an option in BOL... I DO see
CREATE_DATABASE
ALTER_DATABASE
DROP_DATABASE
Any way to fire a server trigger on a database restore?
We're capturing the other server events but would like to also log and be notified of database restores. Thanks, Bruce
if service broker is not an option, how can the trigger and sp(s) called by our trigger act asynchronously to the event that fired the trigger in the first place. We are more concerned with the original event being committed than the actions that follow via the trigger.
CREATE trigger trUpdateGEOData on dbo.BK_Machine after insert, update as updateBK_Machine setBK_Machine.LOC_Street = GEO_Postcode.STraatID, BK_Machine.Loc_City = GEO_Postcode.PlaatsID fromBK_Machine inner join GEO_Postcode on BK_Machine.loc_postalcode = GEO_Postcode.postcode and BK_Machine.LOC_Doornumber >= GEO_Postcode.van and BK_Machine.LOC_Doornumber <= GEO_Postcode.tem inner join inserted on BK_Machine.MachineID = Inserted.MachineID
Now the thing is that a machine not neccesarily needs a location which mean that if postalcode or doornumber is NULL this trigger should clear the street and city columns.
I'm new to sql server (2000) and I have to make some trigger before it will be too late :( I don't have much time to learn it right now. but I'll defiantly do this some time.
I have 2 tables. tbl_A and tbl_B . in tbl_A I have a field named 'money' and another field 'name' .I need to make a trigger that will run whenever the money field updated. the update should be like that: 10% of the money that are in the update statement goes to every row in tbl_A where name='<name that used in update query>'. in addition, the 10% and the name goes to tbl_B
Does anyone have code for a trigger that when the user deletes the record trigger it to insert into another table? Thanks in advance and appreciate your help!
Hello, It is rare that I have a user for Triggers, but I have a special case. In a table named tbl_PaymhistCurrentWeek, I have a column called 'AcctCode'. This column will contain data that will be derived from another column called AcctCodeWithZeros. AcctCodeWithZeros contains an account code with leading zeros. The column AcctCode needs to contain the value in AcctCodeWithZeros, only without the leading zeros.
I tried to set up something in a Derived Column Transformation within SSIS, but I can't use PATINDEX.
Here is the latest version of the Trigger:
***************** CREATE TRIGGER [dbo].[tr_AcctCode] ON [dbo].[tbl_PaymhistCurrentWeek] FOR INSERT AS
BEGIN INSERT tbl_PaymhistCurrentWeek (AcctCode, Co, AccountNoWithZeros) SELECT id.SUBSTRING([AccountNoWithZeros], PATINDEX('%[^0]%', [AccountNoWithZeros]), 14)AS AcctCode, id.Co, id.AccountNoWithZeros FROM Inserted id END **************** The default for AcctCode is an empty string, which is all I get when I insert into the table. AcctCode will be part of a primary key, so I don't think I can set up AcctCode to be a calculated column. What am I doing wrong?
The DDL for the table is as follows: tbl_PaymhistCurrentWeek ( AcctCode varchar(10) Default ' ' Not Null, Co char(2) Not Null, AcctCodeWithZeros varchar(20) Null CONSTRAINT (PK_PaymhistCurrentWeek) Primary Key Clustered (AcctCode, Co) )
Is it possible to have a login that can access a table, which has an Update trigger on a column, and do some updating on another column but not have the trigger fire?
I cannot disable the Trigger. This db is on production and the trigger cannot be taken off. I also cannot bcp the data out and do the updating and bcp back in. The production table must remain as is, however I need to update some cols without the trigger firing.
I am fairly new to using triggers and stored procs in SQL Server and could use some help.
Scenario:
I have a table on SQL Server database running on a Windows server (of course) that contains information about articles. A second server in the shop is set up as a LAMP (Linux, Apache, MySQL, PHP) box. What I would like to do is any time the SQL server table is updated or a record is added, tables in MySQL would then be updated to reflect the changes on the first server. My thought is to try and do this with triggers and a stored proc.
Questions: 1) Assuming MySQL can be accessed through ODBC, can an ODBC connection be set up inside a stored proc or trigger, or would this have to be done through the Windows ODBC Data Sources tool in the control panel?
2) Can a Stored Proc be used in a trigger?
3) Can a Stored Proc call an outside function, such as an API call for third party software?
I'm working on Application that requires me to check and display status of reports running on report server. My application calling ListJobs() function of Job class part of Reporting Services Web Service API. When i run my application i'm getting insufficient previleges error. So i need to find out what type of permission i need to excute ListJobs().
This is very important part of my app. Please help me out.
I am new at ASP.net and I am having problems inserting data using C# in ASP.netI have created a table named "Profile" in the MS sql server database named "MyDataBase". There is a field named "ID" that has data type 'uniqueidentifier'.I am confused how to INSERT data into this data field. I have used MS Access and MYSQL in which there is an option of auto increment so we don't a unique identifier for each record.Please tell me what can I do to If I want to have a uniqueidentifier for each new record I INSERT in the "Profile" table of MS sql server database.While trying to insert, I get following errorsCannot insert the value NULL into column 'ID'and I don't know how I can insert something in this field that is of value type unique identifier.Please help me I will be very thankfull of you.
Is there a type of TRIGGER that I can use to do do post-INSERTprocessing? I've got one table that I never want to block the INSERT,but instead need to do post INSERT processing on. The only way I knowto do this is via a JOB, which isn't ideal in this scenerio.mas
Hi All, I have the code below on Sql 2k table with the column Descriptivetext defined as Text Data Type. The table has Path and Page as the PK I need to set a trigger to update Desciptivetext in other rows with the same path when a one of them chnages. Whenever I run the code, no row is updated
CREATE TABLE [dbo].[AB] ( [PATH] [varchar] (255) NOT NULL PRIMARY KEY CLUSTERED , [PAGE] [varchar] (60) NOT NULL PRIMARY KEY CLUSTERED , [DESCRIPTIVETEXT] [text] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO
CREATE TRIGGER [Update_Staging_Test] ON [dbo].[AB] INSTEAD OF UPDATE AS
Declare @TextPointer as Varbinary (16), @NewTextPointer as Varbinary (16), @Path varchar(255), @Page as varchar(60), @DescriptiveText as varchar(7000) If Update(DescriptiveText) --Get the Path of the record from Inserted Select @Page = Page, @Path = Path , @DescriptiveText = Cast(DescriptiveText as Varchar(7000)) from Inserted
UPDATEAB SETDescriptiveText = Cast (@DescriptiveText as Text) WHEREPath = @Path
Dealing with 3rd party app that is somehow deleting records. Was going to put a delete trigger on the table so we can at least be notified of the delete and save the record. Great idea until I found out the table has a text data type for one of the fields which are'nt allowed on the delete or update portion of the trigger. Unfortunately the text data contains what we really need to retain. Tried doing a convert varchar and that would not work.
Hi,I want to have all-in-one trigger, defined like this:CREATE TRIGGER MyInsertDeleteUpdateHandlerON MyTableFOR DELETE, INSERT, UPDATEASBEGIN(...)ENDNow, how can I tell why this trigger was fired (what event causedtrigger to be fired) - was it DELETE, INSERT or UPDATE?Is there something like this: @@event_type,so I could do for example IF (@@event_type = DELETE) (...)Of course I can create 3 triggers instead of 1, to be sure what eventfired my trigger.I can also count records in _deleted_, _inserted_ tables or to doJOINs with it. But, _inserted_ table is common for UPDATE and INSERTevents..Any suggestions?Thanks in advance.Hubert
My SQL Server 2005 SP4 on Windows 2008 R2 is flooded with the below errors:-
Date 10/25/2011 10:55:46 AM Log SQL Server (Current - 10/25/2011 10:55:00 AM) Source spid Message Event Tracing for Windows failed to send an event. Send failures with the same error code may not be reported in the future. Error ID: 0, Event class ID: 54, Cause: (null).
Is there a way I can trace it how it is coming? When I check input buffer for these ids, it looks like it is tracing everything. All the general application DMLs are coming in these spids.
I have been testing with the WMI Event Watcher Task, so that I can identify a change to a file. The WQL is thus:
SELECT * FROM __InstanceModificationEvent within 30 WHERE targetinstance isa 'CIM_DataFile' AND targetinstance.name = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdventureWorks.bak'
This polls every 30 secs and in the SSIS Event (ActionAtEvent in the WMI Task is set to fire the SSIS Event) I have a simple script task that runs a message box).
My understanding is that the event polls every 30 s and if there is a change on the AdventureWorks.bak file then the event is triggered and the script task will run producing the message. However, when I run the package the message is occurring every 30s, meaning the event is continually firing even though there has been NO change to the AdventureWorks.bak file.
Am I correct in my understanding of how this should work and if so why is the event firing when it should not ?
Server 2003 SE SP1 5.2.3790 Sql Server 2000, SP 4, 8.00.2187 (latest hotfix rollup) We fixed one issue, but it brought up another. the fix we applied stopped the ServicesActive access failure, but now we have a failure on MSSEARCH. The users this is affecting do NOT have admin rights on the machine, they are SQL developers. We were having
Event Type: Failure Audit Event Source: Security Event Category: Object AccessEvent ID: 560 Date: 5/23/2007 Time: 6:27:15 AM User: domainuser Computer: MACHINENAME Description: Object Open: Object Server: SC Manager Object Type: SC_MANAGER OBJECT Object Name: ServicesActive Handle ID: - Operation ID: {0,1623975729} Process ID: 840 Image File Name: C:WINDOWSsystem32services.exe Primary User Name: MACHINE$ Primary Domain: Domain Primary Logon ID: (0x0,0x3E7) Client User Name: User Client Domain: Domain Client Logon ID: (0x0,0x6097C608) Accesses: READ_CONTROL Connect to service controller Enumerate services Query service database lock state
We recently upgraded to SQL 2005 from SQL 2000. We have most of our issues ironed out however about every 1 minute there is a message in the Application Event log and the SQL log that states:
EVENT ID 18456 Login Failed for the users DOMAIN/ACCOUNT [CLIENT: <local machine>]
This is a state 16 message which I thought meant that the account does not have access to the default database. The account is actually the account that the SQL services run under.
Any ideas? We can't seem to figure this one out. We actually upgraded to 2005 from 2000 and had an error appear after every reboot that prevented the SQL Agent from running(This application has failed to start because GAPI32.dll was not found. Re-installing the application may fix this problem.) We did a full uninstall of SQL and reinstalled fresh and restored the databases from .bak files and that is when the EVENT ID 18546 started occuring every minute.
We don't have any SQL heavy hitters here so please be detailed with any possible solutions. That you very much for any help you can provide!
I have recently started working on a OLAP application. The database is around 35 gig, with over half of that space being taken by the indexes. What has happened is previous developers have added indexes to attempt to improve the performance of a select statement (for reporting). Some of the indexes work, but many do not.
I would like to drop the ineffective indices. What I would like is a way to find if indexes are being used, preferably by object name. Is there a third party vendor that can monitor objects and how much they are used? Is there another way to do this using SQL?
How can get an object's dependencies in SQL Server. For example it I have written a procedure which accesses some tables inside it then the procedure is said to be dependent on that table. Or one procedure might call another procedure and hence dependent.
Can I know an object's dependent objects from any system table. I think Oracle has a table USER_DEPENDENCIES which provides this info (I may be wrong :-().
