Persons Table
PersonID int NOT NULL PRIMARY KEY
PersonFatherID int NULL FOREIGN KEY Persons(PersonID)
PersonMotherID int NULL FOREIGN KEY Persons(PersonID)
PersonGeneration int NULL
PersonFirstName nchar(20) NOT NULL
PersonLastName nchar(20) NOT NULL
Spouses Table
SpouseID int NOT NULL PRIMARY KEY
HusbandID int NOT NULL FOREIGN KEY Persons(PersonID)
WifeID int NOT NULL FOREIGN KEY Persons(PersonID)
Persons Table Data
PersonID PersonFatherID PersonMotherID PersonGeneration PersonFirstName PersonLastName
1 1 1 1 Adam Smith
2 2 2 1 Evelyn Smith
3 1 2 2 Caleb Smith
4 NULL NULL 0 Sara Jones
I want to update PersonGeneration column in Persons table when that person is added into Spouses table. For example, if Sara Jones is added as wife to Caleb Smith in Spouses table, then it should update Sara's PersonGeneration column (0) with Caleb's PersonGeneration (2) in Persons table. So, the rule is if PersonFatherID=NULL and PersonMotherID=NULL and PersonGeneration=0, then update this person's PersonGeneration with his/her spouse's PersonGeneration. I am thinking about a trigger or a stored procedure or both. Any help would be greately appreciated.
HiThis problem involves 2 columns in my Product table - ReleaseDate(varchar) and ReleaseClass(varchar)When a new product is Entered, the current date is inserted into ReleaseDate & ReleaseClass = NewWhen product is a month old, I want ReleaseClass to = RecentWhen product is a month 3 mths old, I want ReleaseClass to = NormalWhen the product is a month old, is there a method to automatically run a Stored procedure say to change the ReleaseClass field??any code or links on how i go about this would be appreciated + i've never used a triggerCheers!!
Hi, Please help me to find this answer. We know Trigger are a type of stored procedure,and can be activated whether by a insert ,update or delete event of a table. We also know that stored procedure are quick due to their execution plan which are already stored in the memory once complied. But what about triggers? what is the mechanism of triggers?How they work? And how fast they are from Tsql queries?Is there any mechanism to calculate or measure the efficiency of triggers? Please comment if anybody knows the answers. Thanks!! Joydeep
Hello there, I searched for answers to the above topic, but could not find what I want. My stored procedures and triggers are returning a message based on the result, mostly error messages. How can I get that message using ASP.Net? Should I use an output parameter? Thank you for your help.
I have a trigger that checks if a particular field in an application is being messed with. I am only allowing users to update this field when it is empty. So, I am not allowing them to change the value stored in the field (they will receive an email notification) because a backend operation is going on that could screw things up.
Aside from that, I want the application to refresh the page when the email goes out to go back and display the original contents of the field.
Now, I know this obviously cannot be done from a trigger since it is only dealing with the database. But, does someone have a quick and dirty way of accomplishing this?
I have a requirement to audit tables in a SQL Server database. The tables are dynamically created when the application creates a form and the table holds the form data. So my plan is this, I have worked out the audit table (static) and the trigger. What i'm having issues with is getting the trigger to create from within the stored procedure. So just to recap: the user creates a form in the app, this creates a table and should call this stored procedure. The stored procedure creates the trigger on that table (which begins auditing that table, inserting to the static audit table based on the table name being passed into the stored procedure).
Where im at: I can create the stored procedure. When i go to run the stored procedure, I get the errors after passing the table as a value.
In my opinion it's an error with the correct number of single ticks, but not sure.
The Code: USE [AdventureWorks] GO /****** Object: StoredProcedure [dbo].[spReplaceAuditTrigger] Script Date: 12/06/2011 15:28:50 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROC spReplaceAuditTrigger( @PassedTableName as NVarchar(255) ) AS
I have a problem calling stored procedure in trigger..
When no exception occures stored procedure returns the value but if any exception occures executing that stored procedure then stored procedure will not return any value..
I have handled exception by returning values in case if any..
Here is the stored procedure
CREATE PROCEDURE BidAllDestinations ( @ITSPID int, @DestinationID int, @BidAmount decimal (18,4), @BidTime datetime, @intErrorCode int out )
AS DECLARE @GatewayID int DECLARE @GatewayExist int SET @GatewayID = 0 SET @GatewayExist = 0 SET @intErrorCode = 0
UPDATE BID FOR CORRESPONDING GATEWAY DECLARE GatewayList CURSOR FOR
SELECT Gateways.GatewayID FROM Gateways INNER JOIN GatewayDestinations ON Gateways.GatewayID = GatewayDestinations.GatewayID INNER JOIN ITSPs ON Gateways.ITSPID = ITSPs.ITSPID Where Gateways.ITSPID = @ITSPID AND DestinationID = @DestinationID
OPEN GatewayList
FETCH NEXT FROM GatewayList INTO @GatewayID
IF (@GatewayID = 0)
SET @intErrorCode = 1 ELSE BEGIN -- CHECK @@FETCH_STATUS TO SEE IF THERE ARE ANY MORE ROWS TO FETCH WHILE @@FETCH_STATUS = 0
UPDATE TerminationBids SET BidAmount = @BidAmount, BidTime = getdate()
WHERE GatewayID = @Gatewayid AND DestinationID = @DestinationID
ELSE
INSERT INTO TerminationBids (GatewayID, DestinationID, BidAmount) VALUES (@GatewayID,@DestinationID,@BidAmount)
IF @@ERROR <> 0 BEGIN GOTO PROBLEM CLOSE GatewayList DEALLOCATE GatewayList END
FETCH NEXT FROM GatewayList INTO @GatewayID
END CLOSE GatewayList DEALLOCATE GatewayList
END PROBLEM: BEGIN
SET @intErrorCode = 100
END RETURN @intErrorCode GO
TRIGGER CODE:::
CREATE TRIGGER TR_TerminationBid ON dbo.TerminatorBidHistory FOR INSERT
AS
DECLARE @ITSPID int DECLARE @DestinationID int DECLARE @BidAmount decimal (18,4) DECLARE @BidTime datetime DECLARE @intErrorCode INT DECLARE @DistinationList varchar (8000) DECLARE @DestinationLevel varchar (100) SET @intErrorCode = 0 SET @ITSPID = 0 SET @DistinationList = '' -- CHECK ITPSID' S VALIDITY
SELECT@ITSPID = i.ITSPID, @DestinationID= i.DestinationID, @BidAmount = i.BidAmount, @BidTime = i.BidTime FROM Inserted i INNER JOIN ITSPS ON ITSPS.ITSPID = i.ITSPID INNER JOIN Destinations ON Destinations.DestinationID = i.DestinationID
EXEC BidAllDestinations @ITSPID,@DestinationID,@BidAmount,@BidTime, @intErrorCode = @intErrorCode output SELECT @intErrorCode Following should return value for @intErrorCode if any exception occures
I'm trying to come up with a Stored Procedure or a Trigger to Sum up monthly =Expenses to the YTDExpenses Column. Can Someone help please.
I have a Monthlyexpense column. How do I Sum up this column and put the Total in my ytdexpenses column. Do I use a stored procedure, because I want the monthlyExpenses to SUm up every time I submit a monthly expense to the database and siplay in the ytdExpenses Column. When I Write a Query all of the rows in the ytdExpenses shows the same amount and do not total up every time I submit to the database. Help please.
Hi AllI need opinions on how to approach my task.I currently have 3 tables: the master table, the archive and a temptable.MASTER: has 3 fields ProductID and ProductNo and ReleasedARCHIVE: Has 3 ProductID, ProductNo, SoldDateTEMP: ProductID, ProductNo, SoldDateI have a trigger on the master table upon deletion to archive. This istriggered from a seperate routine from a vb app to delete a recordreal time.CREATE Trigger Archive_Proc On dbo.MASTERFor DeleteAsDeclare @iDate As DateTimeSet @iDate = GetDate()If @@RowCount = 0 Returnset Nocount onInsert Into ARCHIVE(ProductID, ProductNo, SoldDate)Select ProductID, ProductNo, @iDate from deletedMy problem is that I have a temp table that gets filled from aseperate transaction.It needs to be matched against the master tablethen deleted at both master and temp. but the issue is that the temptable contains its own SoldDate value that needs to be archived.Q 1: if I use a stored proc. how do i pass the SoldDate value to thetrigger as Triggers dont use GVs.Q 2: How do I set up the stored procedure to delete with multipletables. I can get it to UPDATE but not delete....CREATE PROCEDURE COMPARESOLD@Pool SmallintASSet NoCount onUpdate MASTERSet Released = 2From TEMP, MASTERWhere TEMP.ProductNo = MASTER.ProductNoAND TEMP.ProductID = MASTER.ProductIDAND INVENTORY.Released = 1hopefully someone can lead me to the right direction...Thanks
I am trying to test a simple trigger on insert and it does not work when I call EXEC sp_send_cdosysmail. However, the stored procedures does work if I right-click on it and select Execute Stored Procedure.
Below is a simple version of the trigger I am trying to implement. I know it works in SQL Server 2000 and 2005 but can't seem to get it to work in SQL Server 2005 Express. Any help is greatly appreciated!
ALTER TRIGGER [dbo].[trig_Tableinsert] ON [dbo].[Table] FOR INSERT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements.
Print 'Hello'
-- Insert statements for trigger here EXEC sp_send_cdosysmail some@one.com', 'notify@me.com','New Insert', 'test'
Persons Table PersonID int NOT NULL PRIMARY KEY PersonFatherID int NULL FOREIGN KEY Persons(PersonID) PersonMotherID int NULL FOREIGN KEY Persons(PersonID) PersonGeneration int NULL PersonFirstName nchar(20) NOT NULL PersonLastName nchar(20) NOT NULL
Spouses Table SpouseID int NOT NULL PRIMARY KEY HusbandID int NOT NULL FOREIGN KEY Persons(PersonID) WifeID int NOT NULL FOREIGN KEY Persons(PersonID)
Persons Table Data PersonID PersonFatherID PersonMotherID PersonGeneration PersonFirstName PersonLastName 1 1 1 1 Adam Smith 2 2 2 1 Evelyn Smith 3 1 2 2 Caleb Smith 4 NULL NULL 0 Sara Jones
I want to update PersonGeneration column in Persons table when that person is added into Spouses table. For example, if Sara Jones is added as wife to Caleb Smith in Spouses table, then it should update Sara's PersonGeneration column (0) with Caleb's PersonGeneration (2) in Persons table. So, the rule is if PersonFatherID=NULL and PersonMotherID=NULL and PersonGeneration=0, then update this person's PersonGeneration with his/her spouse's PersonGeneration. I am thinking about a trigger or a stored procedure or both. Any help would be greately appreciated.
We have a requirement in the project to do auditing for some of the tables. We have come across different approaches of implementing auditing.
The following are the approaches:
Create a generic stored procedure to do auditing. Call this procedure whenever any tables that require auditing have insert/update/delete operation performed on it. Auditing and the DML operation should be part of a single transaction. Create a generic CLR trigger to do the auditing. The CLR trigger can be attached to the tables that require auditing. Create separate audit tables and triggers for each table which require auditing.
I would like to know whether there are any disadvantages of using triggers in production server. Could anyone please help me in identifying the best approach for implementing auditing?
Will there be any situation when the DML statements execute and the corresponding trigger fails?
Is there any performance degradation on using triggers for auditing compared to including the auditing logic implemented in the stored procedures? We have been advised not to use triggers in production environment. But we are not clear about the reason for this.
PLEASE NOTE THAT I DO NOT WANT TO REPLACE STORED PROCEDURE WITH TRIGGERS TO IMPLEMENT BUSINESS LOGIC. BUT IN THE SCENARIO THAT I DISCUSSED, I NEED TO CAPTURE THE LOG INFORMATION WHENEVE A DML STATMENT IS EXECUTED AGAINST THE TABLES. WHAT IS THE PREFERRED APPROACH HERE...USING TRIGGERS OR STORED PROCEDURES?
In general, is there any disadvantage in using triggers for auditing? Has anyone faced any issues with triggers? (Triggers not invoked during DML operations or any performance related issues) Please let us know.
I have 3 tables...TableA, TableB, TableC TableA - Personal InformationPersonalInfoId (Primary) , First Name,Last NameTableB - Personal Information To Department IDReferenceID, FKPersonalInfoId, FKDepartmentIdTableC - DepartmentDepartmentId, DepartmentNameI am coding Asp.Net VB using VWD express with Sql Server Express. I know how to create a stored procedure to delete, insert and even update a record in TableA, TableB, TableC respectively.If I need to delete a record in TableC, which has a related record in TableB, I have read that I need to use a Trigger. I never have used a Trigger and it is new to me. Can someone point me a way on how to use one in this case of my deleting scenario. Pretty much, if a user clicks on a delete button, and deletes a record in my TableC, I dont want a FKDpartmentId in my TableB that doesnt exist anymore because it was deleted in TableC or prevent a user from deleting that record till the relationship in TableB is no longer valid. In the same vain, If I have a input form which ask the user to enter their First Name and Last Name and Department, i would like to add those records in TableA for First and Last Name, TableB for the Department. Once again, how do I create a Trigger that if I insert a record in Table A to also insert the information for Department in Table B, if its successful in my stored procedure. Hope that made sense.Thanks.
I want to disable a trigger on a table in a database from inside a stored procedure in another database. Can I disable then enable? Do I have to drop then recreate the trigger? How do I code it? I've tried several ways but I can't get it right...
I need to create either a trigger or stored procedure in SQL server 2005(hopefully someone can tell me).. Here is what I need to happen: I have a table with orders that are generated from a website. After the transaction is completed, I need have the record that was just created also copy to another table. There is a field called flag and the values in this field are either 1 or 2. Imediatly after the transaction occurs, I need the records where flag = 1 to copy to this other table. How would I go about doing this?
DESCRIPTION: I have an FTP server set up to log via ODBC into a table FTPLog. The trigger on table FTPLog fires when new files are received to process and load the file via a stored procedure.
CREATE TRIGGER tr_new_file ON FTPLog AFTER INSERT AS SET NOCOUNT ON DECLARE @filename varchar(50), @logtime datetime DECLARE c1 CURSOR FOR SELECT filename, logtime FROM inserted OPEN c1 FETCH NEXT FROM c1 INTO @filename, @logtime WHILE @@fetch_status = 0 BEGIN EXEC sp1 @filename, @logtime FETCH NEXT FROM c_inserted INTO @filename, @logtime END CLOSE c1 DEALLOCATE c1 END
PROBLEM: There are multiple problems with this setup. The first problem is that when the stored procedure gets executed it takes a long time to process the file and the FTP server never returned a completion code to the ftp client and ended with a connection time out from the client. My users keep asking if the FTP failed but it didnt fail. The server returned a completion code too late.
PROBLEM2: When multiple files are ftp to the server on the same session, only the first one gets process. Even though my code loops through all the records because the processing takes a long time the second one never gets executed. If I replace the EXEC sp1 statement with a PRINT statement then it's working fine.
HI ALL, I AM USING SQL SERVER 2005. I HAVE RETURN A RECURSIVE FUNCTION TO FIND OUT WHETHER THE NEXT DATE DOES NOT FALL WITHIN HOLIDAYS BUT I AM GETING THIS ERROR Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
THE CODE I USED IS
alter FUNCTION [dbo].[GetNextDay](@dt datetime , @empcode varchar(50) ) RETURNS datetime AS BEGIN DECLARE @zoneid VARCHAR(50) declare @lvlflag varchar(50) declare @utdt DATETIME DECLARE @RETDT DATETIME DECLARE @COMPDT DATETIME
Select @lvlflag= b.ulm_user_field_flag from bbraun_emis.dbo.emp_reference a join bbraun_emis.dbo.user_lvl_master b on b.ulm_user_lvl_id = a.ER_USER_LVL and a.er_emp_code = @empcode SELECT @zoneid = ZONECODE FROM bbraun_emis.dbo.VWREGIONLINK WHERE CITYCODE IN (SELECT DISTINCT HM_CITY_CODE FROM bbraun_emis.dbo.HOSP_MASTER WHERE HM_HOSP_CODE IN (SELECT HER_HOSP_CODE FROM bbraun_emis.dbo.HOSP_EMP_REL WHERE HER_EMP_CODE in(@EMPCODE))) select @compdt = holiday_date from oriffconnect.dbo.holiday_master where zone_code = @zoneid and field_staff = @lvlflag and holiday_date = @dt
if(@@ROWCOUNT = 0) begin
Select @utdt = DATEADD(dd,1,@utdt) SeT @utdt = ([dbo].[GetNextDay](@utdt , @empcode)) end IF(@@ROWCOUNT <> 0) begin set @utdt = @dt end Select @RETDT = @utdt RETURN @RETDT
Hi, I need to call a webservice directly from a tigger or stored procedrue instead of creating a window service to read from a table then call the webservice .Is it possible ? if yes, please i need your support. Thanks,
I have a problem with an existing stored procedure that is used to insert a new entry to a table (using an Insert statement).
I have been using the @@Identity global variable to return the identity column (id column) back to the calling routine. This has worked fine for years until recently an ‘after insert Trigger’ has been added to the table being updated.
Now the @@Identity is returning the identity value of the trigger that was called instead of the original table insert.
Does anyone know how I can code around this issue (without using a select statement within my stored proc, as these have been known to cause locks in the past).
I need to come up with a script that when executed it will create a stored procedure and trigger along with permissions. Is there a way to make this into a package. Any ideas?
What is the syntax for creating a update trigger and passing the values which were updated to a stored procedure on a linked server?? Specifically need syntax for updated value, as well as the syntax for executing the stored proc on the linked server.
I am maintaining an application where most of the business rules are inTriggers, Stored Procedures and User Defined Functions. When a bugarises, it can get very tedious to debug. Today for example, I wantedto modify a function that was being called by a trigger. The problemis that I don't want to change the function, for fear that it is beingcalled by one of the other SP's or triggers in the database (there arehundreds of them)Essentially, I need a tool that allows me to view where functions andsp's are being referenced from. At the very least, I'd like to performa "full text search" in the database objects, so that let's say I havea function named "fn_doSomething", I can search the schema for thisstring and get all the places where it appears.As you can see, I'm in the dark here. I've never worked on a systemwhere all business rules are at the database level. If you know of atool that does what I describe above, or anything else that wouldfacilitate my life, please let me know!Thanks for your help,Marc
I have to control my business rules in a Instead of Insert, Update Trigger.
Since the Control Flow is quite complicated I wanted to break it into stored procedures that get called from within the trigger.
I know that Insert Statements embedded in a Instead of Trigger do not execute the Insert of the trigger you are calling.
But... If I embed stored procedures that handle my inserts in the Instead of Insert trigger call the trigger and put in a endless loop or are the stored procedure inserts treated the same as trigger embedded inserts.
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
I have stored procedure on Server A which goes to ServerB to check and update table and then update on Server A as well.I have Trigger which suppose to execute stored procedure (as i mentioned above). But it failed with this error:--
Trigger code:-- CREATE TRIGGER [tr_DBA_create_database_notification] ON ALL SERVERÂ AFTER CREATE_DATABASE ASÂ --execute dbadmin.dbo.usp_DBA_Refresh_DBAdmin_Tables
Error:--The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "xxx" was unable to begin a distributed transaction.Process ID 62 attempted to unlock a resource it does not own: DATABASE 21. Retry the transaction, because this error may be caused by a timing condition. If the problem persists, contact the database administrator.
Same stored procedure, if i execute manually or if i create sql job and execute this stored procedure, it works just fine..In trigger also, if i execute start job which has stored procedure, it works.My question is,why it failed when i execute stored procedure in TRIGGER.
I have writen a Function which call's the same function it self. I'm getting the error as below.
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32). Can any one give me a solution for this problem I have attached the function also.
CREATE FUNCTION dbo.GetLegsFor(@IncludeParent bit, @EmployeeID float) RETURNS @retFindReports TABLE (EmployeeID float, Name nvarchar(255), BossID float) AS BEGIN IF (@IncludeParent=1) BEGIN INSERT INTO @retFindReports SELECT MemberId,Name,referredby FROM Amemberinfo WHERE Memberid=@EmployeeID END DECLARE @Report_ID float, @Report_Name nvarchar(255), @Report_BossID float DECLARE RetrieveReports CURSOR STATIC LOCAL FOR SELECT MemberId,Name,referredby FROM Amemberinfo WHERE referredby=@EmployeeID OPEN RetrieveReports FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID WHILE (@@FETCH_STATUS = 0) BEGIN INSERT INTO @retFindReports SELECT * FROM dbo.GetLegsFor(0,@Report_ID) INSERT INTO @retFindReports VALUES(@Report_ID,@Report_Name, @Report_BossID) FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID END CLOSE RetrieveReports DEALLOCATE RetrieveReports
I am running this query "delete from ims_domains where id=61" and got the error Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)
Please let me know what should be the reason? Thanks, Ravi
