MS SQL Server :: Maximum Stored Procedure, Function, Trigger, Or Vi

Maximum Stored Procedure, Function, Trigger, Or View Nesting Level Exceeded (limit 32
Hi,

I face this error when i try to run my store procedure.
The sample of store procedure as following:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE sp_addUserAccess
with encryption
AS
SET NOCOUNT ON

DECLARE @COUNTER INT
SET @COUNTER = 0

DECLARE @i_compId INT
BEGIN
DECLARE C1 SCROLL CURSOR FOR
SELECT i_compId
FROM ltd_cms_company WHERE (i_owner = 176 or i_owner = 268) AND ti_recStatus = 1
END

OPEN C1
FETCH ABSOLUTE @COUNTER FROM C1 INTO
@i_compId

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO ltd_cms_userAccess ( i_loginId, i_groupId, i_compId, ti_updComp, ti_updLog, ti_updAccess, ti_owner, ti_acctMgr, ti_updContact, ti_updEvent )
VALUES ( 124, 0, @i_compId, 1, 1, 1, 1, 1, 1, 1)

SET @COUNTER = @COUNTER + 1
FETCH ABSOLUTE @COUNTER FROM C1 INTO
@i_compId
END

CLOSE C1
DEALLOCATE C1
SET NOCOUNT OFF

anyone can help me identify this error?

Thanks

Regards,
Jojomay

View Replies !

Maximum Stored Procedure, Function, Trigger, Or View Nesting Level Exceeded (limit 32)
Hello,

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

View Replies !

Urgent : Maximum Stored Procedure, Function, Trigger, Or View Nesting Level Exceeded
Hi all,

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

RETURN
END

View Replies !

Maximum Stored Procedure, Function, Trigger, Or View Nesting Level Exceeded (limit 32).
I have created a delete trigger in Table1 and Table2. Once I delete a certain record in Table1 it will also delete that record in Table2 or vice versa. But once i delete certain record either in Table1 or Table2 it will create an error "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).". Can you help me on this?

View Replies !

Maximum Allowed Stored Procedure In SQL Server
Here is something that perhaps a lot of you out there wonder about..

My company is on a large Enterprise Project.
The database plans for this to be fully supported predict 1000+ stored procedures.

Question:
Will there be any limitation for an SQL Server 2000 to handle so many stored procs?

and if there is a limitation what will we have to do in order to work around it?

View Replies !

Maximum Concurrency Of 2 For Outbound Network Connections From CLR-stored Procedure?
Hi

I've implemented an SQL-CLR stored procedure which makes an HTTP connection using the WebClient class to a server where the processing on the web server takes 10 seconds.

If I fire off a bunch of requests to the stored procedure, e.g. 80 in 1 second I noticed that SQL Server 2005 (Express Edition) only allows 2 concurrent network connections at a time.

I can confirm this by looking at the timing of the results when they come back, basically I get 2 results every 10 seconds and by doing a 'netstat -a' on the web server I notice that there are never more than 2 HTTP connections from the SQL Server at a time.

Is this some sort of sheduling policy in terms of the number of concurrent external network accesses allowed by CLR objects running in SQL Server? Or some side effect of the way the WebClient class blocks in terms of waiting for a network response?

I was expecting all 80 of the requests to block waiting on a network response almost immediately and then for all 80 of them to pretty much complete and return 10-11 seconds later.

[Microsoft.SqlServer.Server.SqlProcedure]

public static int CreditAuthorisation(string Name, decimal Amount)

{

WebClient client = new WebClient();

string result = client.DownloadString("http://someserver/Test.aspx");

return int.Parse(result);

}

Cheers

View Replies !

Retrieving Result Set From Dynamically Called Stored Procedure Or Function In A Function
Is there any way I can retrieve the result set of a Stored Procedurein a function.ALTER FUNCTION dbo.fn_GroupDeviceLink(@groupID numeric)RETURNS @groupDeviceLink TABLE (GroupID numeric, DeviceID numeric)ASBEGINDeclare @command nvarchar(255)SELECT @command = Condition// @command is an SQL string or stored procedue nameFROM DeviceGroupWHERE GroupID = @groupIDINSERT @groupDeviceLinkEXEC @commandRETURNENDIs there any way i can do anything like this. @command is a variableholding the name of a stored produre. I need to run that storedprocure and return the values in such a way that they can be used in aSELECT StatementMy goal is SELECT * FROM Device INNER JOINdbo.fn_GroupDeviceLink(@groupID) ON ....this fn_GroupDeviceLink should run the proper stored procedure andreturn the values. What i also want to do is play with that result setof the specific stored procedure before i return it. Is this possible?If not, what is the work arround?ThanksMark

View Replies !

Gridview / SqlDataSource Error - Procedure Or Function &<stored Procedure Name&> Has Too Many Arguments Specified.
Can someone help me with this issue? I am trying to update a record using a sp. The db table has an identity column. I seem to have set up everything correctly for Gridview and SqlDataSource but have no clue where my additional, phanton arguments are being generated. If I specify a custom statement rather than the stored procedure in the Data Source configuration wizard I have no problem. But if I use a stored procedure I keep getting the error "Procedure or function <sp name> has too many arguments specified." But thing is, I didn't specify too many parameters, I specified exactly the number of parameters there are. I read through some posts and saw that the gridview datakey fields are automatically passed as parameters, but when I eliminate the ID parameter from the sp, from the SqlDataSource parameters list, or from both (ID is the datakey field for the gridview) and pray that .net somehow knows which record to update -- I still get the error. I'd like a simple solution, please, as I'm really new to this. What is wrong with this picture? Thank you very much for any light you can shed on this.

View Replies !

SqlDataSource.SelectParameters Causing Procedure Or Function Stored Procedure Has Too Many Arguments Specified.
Hi everybody, I am having trouble how to fixed this code. I am trying to supply the parameterinside a stored procedure with a value, and displays error message shown below. If I did not supply the parameter with a value, it works. How to fix this?Error Message:Procedure or function <stored proc name> has too many arguments specified.Thanks,den2005
Stored procedure:

Alter PROCEDURE [dbo].[sp_GetIdeaByCategory]
@CatId int = 0
AS
BEGIN
SET NOCOUNT ON;

Select I.*, C.*, U.* From Idea I inner join IdeaCategory C on I.CategoryID = C.IdeaCategoryID inner join Users U on I.UserID = U.UserID Where I.CategoryID = @CatId Order By LastModifiedDate Desc
End

oDataSource.ConnectionString = constr;
oDataSource.SelectCommand = storedProc;//storedproc - sp_GetIdeaByCategory
oDataSource.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
oDataSource.SelectParameters.Add("@CatId", catId);
gdvCategories.DataSourceID = oDataSource.ID;

gdvCategories.DataBind(); <<--- Error occured here

View Replies !

Procedure Or Function 'stored Procedure Name' Expects Parameter Which Was Not Supplied
Has anyone encountered this before?
Procedure or Function 'stored procedure name' expects parameter '@parameter', which was not supplied.
It seems that my code is not passing the parameter to the stored procedure.
When I click this hyperlink:
<asp:HyperLink
ID="HyperLink1"
Runat="server"
NavigateUrl='<%# "../Division.aspx?CountryID=" + Eval("CountryID")%>'
Text='<%# Eval("Name") %>'
ToolTip='<%# Eval("Description") %>'
CssClass='<%# Eval("CountryID").ToString() == Request.QueryString["CountryID"] ? "CountrySelected" : "CountryUnselected" %>'>
</asp:HyperLink>
it is suppose to get the country name and description, based on the country id.
I am passing the country id like this.
protected void Page_Load(object sender, EventArgs e)
{
PopulateControls();
}
private void PopulateControls()
{
string countryId = Request.QueryString["CountryID"];
if (countryId != null)
{
CountryDetails cd = DivisionAccess.GetCountryDetails(countryId);
divisionNameLabel.Text = cd.Name;
divisionDescriptionLabel.Text = cd.Description;
}
}
To my app code like this:
public struct CountryDetails
{
public string Name;
public string Description;
}
public static class DivisionAccess
{
static DivisionAccess()
public static DataTable GetCountry()
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetCountry";
return GenericDataAccess.ExecuteSelectCommand(comm);
}
public static CountryDetails GetCountryDetails(string cId)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetCountryDetails";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@CountryID";
param.Value = 2;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
CountryDetails details = new CountryDetails();
if (table.Rows.Count > 0)
{
details.Name = table.Rows[0]["Name"].ToString();
details.Description = table.Rows[0]["Description"].ToString();
}
return details;
}

As you can see I have two stored procedures I am calling, one does not have a parameter and the other does. The getcountry stored procedure returns the list of countries in a menu that I can click to see the details of that country. That is where my problem is when I click the country name I get
Procedure or Function 'GetCountryDetails' expects parameter '@CountryID', which was not supplied
Someone please help!

Thanks Nickdel68

View Replies !

Calling A Stored Procedure Or Function From Another Stored Procedure
Hello people,

When I am trying to call a function I made from a stored procedure of my creation as well I am getting:

Running [dbo].[DeleteSetByTime].

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.TTLValue", or the name is ambiguous.

No rows affected.

(0 row(s) returned)

@RETURN_VALUE =

Finished running [dbo].[DeleteSetByTime].

This is my function:

ALTER FUNCTION dbo.TTLValue

(

)

RETURNS TABLE

AS

RETURN SELECT Settings.TTL FROM Settings WHERE Enabled='true'

This is my stored procedure:

ALTER PROCEDURE dbo.DeleteSetByTime

AS

BEGIN

SET NOCOUNT ON

DECLARE @TTL int

SET @TTL = dbo.TTLValue()

DELETE FROM SetValues WHERE CreatedTime > dateadd(minute, @TTL, CreatedTime)

END

CreatedTime is a datetime column and TTL is an integer column.

I tried calling it by dbo.TTLValue(), dbo.MyDatabase.TTLValue(), [dbo].[MyDatabase].[TTLValue]() and TTLValue(). The last returned an error when saving it "'TTLValue' is not a recognized built-in function name". Can anybody tell me how to call this function from my stored procedure? Also, if anybody knows of a good book or site with tutorials on how to become a pro in T-SQL I will appreciate it.

Your help is much appreciated.

View Replies !

Trigger And Stored Procedure
What is trigger!what is differance beetween trigger and stored-Procedure!

View Replies !

Stored Procedure Or Trigger Or Both Or ???
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!!

View Replies !

Trigger Vs Stored Procedure
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

View Replies !

Function Vs Stored Procedure
I know this is a stupid question (actually, maybe its not..?)

They seem to be identical in some ways, but not available to the outside world. what are some differences?

View Replies !

When To Use A Function And When To Use A Stored Procedure.
Hi,

I thought that procedures do not return a value: well they only return a 1 or 0 depending on whether the procedure executed correctly. Functions are what you're supposed to use when you want to get a result set of some sort: ie a table or a scalar value.

Apparently that is not the case becuase you can return values from procedures. I've attempted to find out what the differences are between the two and when it is appropriate to use a procedure and when it is appropriate to use a function but I'm still unsure. Can someone please tell me the difference.

Thanks.

Przemek

View Replies !

Function From Stored Procedure
Hi Everyone,

i want to create a function in Stored procedure and then call this function
that returns true or false from a stored procedure .
is this possible?

please help me if you know something that can help me.

thanks

Tvin

View Replies !

Get Return Value From Stored Procedure And Trigger
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.

View Replies !

How To Approach (Trigger-Stored Procedure )
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

View Replies !

Trigger Executing A Stored Procedure
I am having a issue with executing a stored procedure from a trigger. I have two tables, a staging table, and a live table. The staging table has a FOR INSERT trigger on in that executes a stored procedure, and also uses some error handling. However, I have placed a primary key on the live table, and I'm inserting data into the table that will violate that primary key. This is to test that the transaction gets rollback correctly. However, the data never gets inserted into the staging table, nor will any raiserror kick off. The stored procedure gets called, and errors out with out calling my if @@ERROR <> 0 statement, or inserting data into my staging table. How can I still insert data into the staging table and call the @@ERROR statement? Can I check the constraints of the table called by the stored procedure before actually calling the stored procedure? I have also tried the INSTEAD OF INSERT as well, that doesn't work either. Thanks.

View Replies !

Calling Stored Procedure In Trigger
Hi

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

BEGIN

SELECT@GatewayExist = Gatewayid
FROMTerminationBids
WHEREGatewayid = @Gatewayid AND DestinationID = @DestinationID

IF @GatewayExist > 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

Any one can help what is wrong with it?

Thanks

View Replies !

How Return A Value From Trigger To Stored Procedure?
I have a little problem. I have a trigger on a table TakesCourse that checks how many points a student currently is taking. If the current number + the incomming value is greater than 30 I want to return a value to the stored procedure that has the insert statment that fires the trigger. Then in the stored procedure I check this value and if its not ok, then I return -1 with the return parameter.

But I don't know how to send data back to the SP from the trigger.

I want to grab the error in the SP, I dont want to use, say a raiserror straight back to the client.

Any help appriciated.

/Magnus

Pasting in my current code:
CREATE TRIGGER TrigCheckPoints ON TakesCourse
FOR INSERT, UPDATE
AS

SET NOCOUNT ON

IF UPDATE(studentID)
BEGIN

DECLARE @studentIDinsINT
DECLARE @courseIDinsINT
DECLARE @pointsNowINT
DECLARE @pointsCourseINT

/* Get studentID and courseID being inserted */
SELECT @studentIDins=studentID, @courseIDins=courseID
FROM inserted

/* Get students total points now */
SELECT @pointsNow=SUM(C.points)
FROM TakesCourse TC
INNER JOIN Course C ON TC.courseID= C.CourseID
WHERE TC.studentID=@studentIDins

/* Replace NULL with 0 */
IF @pointsNow IS NULL
BEGIN
SELECT @pointsNow=0
END

/* Get points of course being inserted */
SELECT @pointsCourse=points
FROM Course
WHERE courseID=@courseIDins

IF (@pointsNow+@pointsCourse) > 30
BEGIN
/* Returning -1 means failed */
RETURN -1 --this needs to be replaced somehow
END
/* Returning 0 means OK */
Return 0 --this needs to be replaced somehow
END

Problem is with the return. You cant use values with return in a trigger. How do I send data back? with a temptable?

My SP code:
CREATE PROCEDURE TakesCourseInsert
(
@studentID int,
@courseOccasionID int,
@courseID int)
AS
BEGIN
SET NOCOUNT ON

BEGIN TRAN
/* SELECT NULL needed to be able to return the return parameter correctly */
SELECT NULL

INSERT INTO TakesCourse(studentID,
courseOccasionID,
courseID)
VALUES(@studentID,
@courseOccasionID,
@courseID)
--Grab trigger return value here somehow
IF (@triggererror!=0)
BEGIN
ROLLBACK TRAN
RETURN @triggererror
END

COMMIT TRAN
RETURN 0
END

View Replies !

Trigger Calling Stored Procedure???
can a trigger firing cause a stored procedure to execute!! if this can be done then I will have more questions to follow! thanks, Scott

View Replies !

Fire Trigger From Within A Stored Procedure Possible?
Is this possible?

View Replies !

Trigger Or Stored Procedure Question
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?

Thanks

View Replies !

Trigger Starting Stored Procedure
CREATE TRIGGER check_availablerooms ON Reservation
AFTER INSERT
AS
DECLARE @Startdate varchar(20)
DECLARE @Enddate varchar(20)
SELECT @Startdate = ArrivalDate FROM INSERTED
SELECT @Enddate = DepatureDate FROM INSERTED
IF (@Startdate <> 'NULL' AND @EndDate <> 'NULL')
BEGIN
EXECUTE print_availablerooms @Startdate, @Enddate
END

Made this trigger so that all available rooms in the inserted period should show up on the screen. The trigger is working fine in Query Analyzer, but when i try to use it in Access it doesn't produce the result i want.

I want the trigger to run the stored procedure using the arrivaldate and departuredate entered in access as the input arguments.

Does anyone have a solution to this problem? I hope someone can post a code-example or a well described solution to this problem.

View Replies !

Auditing - Trigger Or Stored Procedure

Hi All,

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.

Thanks,

View Replies !

Trigger Or Stored Procedure Question
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

Spouses Table Data
SpouseID HusbandID        WifeID
1             1                       2
2             3                       4

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.

Thanks,
Mohan John

View Replies !

Execute A Trigger From Stored Procedure

Hi,

Is it possible to run trigger from a stored procedure?

View Replies !

Calling A Stored Procedure In A Trigger
Hello,

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'

END

Thanks!

View Replies !

Ytd Expenses---Trigger Or Stored Procedure
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.

monthlyExpenses ytdExpenses
$1,000 $1,000
$2,000 $3,000
$3,000 $6,000
$2,000 $8,000
$5,000 $13,000

View Replies !

Calling A Function From A Stored Procedure
Hello all,
I'm trying to construct a select statement in a stored procedure that filters based on the returned values of a number of functions. My function works fine, but when I try to call the function from the stored procedure I get an error.
I'm going to try explain the thought process behind what I'm doing. Hope I make enough sense.The purpose of the stored procedure is to perform a wildcard search on a tool. The tool contains a number of FK that link to different tables (e.g., manufacturer, vendor). So I'm creating functions that also search the manufacturer and vendor and return the matching IDs.
Example of tool SELECT statement:SELECT tool_number, tool_description
FROM tool
WHERE tool_manufacturer IN (UDFmanufacturer_SearchName(@search_string)
This gives me an error:'UDFmanufacturer_SearchName' is not a recognized built-in function name.
Function code (removed some wrapping code for simplicity):SELECT manufacturer_id
FROM manufacturer
WHERE manufacturer_name LIKE '%' + @search_string + '%'These statements both work if I run a independent query: SELECT *
FROM UDFmanufacturer_SearchName('mol') SELECT *
FROM tool
WHERE tool_manufacturer IN (SELECT *FROM UDFmanufacturer_SearchName('mol')) This code fails:SELECT *
FROM ato_tool
WHERE ato_tool_manufacturer IN (UDFmanufacturer_SearchName('mol'))
I'm stuck. I haven't been able to find anything that shows me where I'm going wrong. Any thoughts or suggestions are appreciated. Thanks,Jay

View Replies !

Call Function From Stored Procedure
Hi All,
I'll admit that I'm not the greatest at stored procedure/functions but I want to learn as much as possible. So I have two questions:
1) I had VS2005 autogenerate a sqldatasource that created Select/Insert/Update stored procedures. When Updating a record and calling the stored procedure, I want to query another table (we'll call it tblBatchNo) that has only one record, Batchno. I want to put that current batchno into the Update statement and update the record with the current batchno. Can someone point me in the right direction? Remember that I'm still a beginner on this subject.
2) Can someone provide any links to online tutorials on t-sql?
Thanks in advance.
Curtis

View Replies !

Differance Stored Procedure And Function
hello
what is differance beetween stored-procedure and Function
when use of stored rpocedure and function

View Replies !

Stored Procedure - Date Function
Hi, I ran into some problem here. The case scenerio is supposed to be like this:

- Each member can only make one appointment at any one time and only
make another appointment after the existing appointment expired.
- Each member is allowed to make an appointment at any time and must be at least 5 days in advance.

I managed to do the reservation for at least 5 days in advance but I
can't allow the member to make only one appointment. The member can
keep making appointments even though the existing appointment has not
expired. Can someone pls help? Thanks!

ALTER PROCEDURE spReserveAppt(@AppDate DATETIME, @AppTime CHAR(4), @MemNRIC CHAR(9))
AS

BEGIN
IF NOT EXISTS(SELECT MemNRIC FROM DasMember WHERE MemNRIC = @MemNRIC)
 RETURN -300

BEGIN
IF EXISTS
 (SELECT COUNT(@MemNRIC)
 FROM DasAppointment
 WHERE (DATEDIFF(DAY, GETDATE(), @AppDate) < 5)
 GROUP BY MemNRIC
 HAVING COUNT(@MemNRIC) <> 0)
 RETURN -301

ELSE IF EXISTS
 (SELECT MemNRIC
 FROM DasAppointment
 WHERE (DATEDIFF(DAY, @AppDate ,GETDATE()) > GETDATE()))
 RETURN -302

END
END

INSERT INTO DasAppointment(AppDate, AppTime, MemNRIC) VALUES (@AppDate, @AppTime, @MemNRIC)

IF @@ERROR <> 0
 RETURN @@ERROR

RETURN

DECLARE @status int
EXEC @status = spReserveAppt '2005-08-16', '1900', 'S1256755J'
SELECT 'Status' = @status

View Replies !

Use Function From Asp File In Stored Procedure
Hello everybody,I have a file with several asp functions where some depend on others.Some of the functions I will need to transform a string value.How can I include these functions in a stored procedure? (in SqlServer 2000)Do I have to add all the functions as userdefined functions?Or is possible to reference the external file like include files inasp?Thank you for your help!Regards,Michael

View Replies !

ASP/SQL Stored Procedure/ASP Function Error
Hey I have the following Stored Procedure

CREATE PROCEDURE spGetOrderCount
(
@search varchar(1000) = default
)

AS

SET NOCOUNT ON

/* Setup search string */
IF (@search <> '')
BEGIN
SET @search = 'WHERE' + @search
END

/* Create a temporary table */
CREATE TABLE #TempTable
(
row int IDENTITY,
totalCount int
)

/* Insert the search results into query */
EXEC
(
'INSERT INTO #TempTable([totalCount])' +
'SELECT COUNT(*) AS totalCount ' +
'FROM tblOrders' + @search
)

/* Extract the wanted records from the temporary table */
SELECT[totalCount],
RecordsLeft =
(
SELECT COUNT(*)
FROM #TempTable TI
)
FROM#TempTable

SET NOCOUNT OFF

RETURN
;
GO

And then the following function which specifies the where clause of the statement

function getOrderCount(strDate, strStatusList)
getOrderCount = 0
dim objRS, objSP, strWhereClause

if isDate(strDate) and len(strStatusList) > 0 then
'# Filter on order status

'# Filter on date clause
strWhereClause = "(tblOrders.orderDate >= " & sqlServerDate(strDate) & ")"

'#GET order count
Set objSP = SQLGetProcedure("spGetOrderCount")
SQLSetProcedureParam objSP, "search", strWhereClause

Set objRS = SQLExecuteProcedure(objSP)

if not objRS.eof then
getOrderCount = objRS("totalCount")
end if

'# Free resources
deleteRecordset(objRS)
deleteObject(objSP)
end if

end function

When I do this together I get the following error on my ASP Page

Microsoft OLE DB Provider for SQL Server error '80040e14'

Line 1: Incorrect syntax near '.'.

/sigma_eircommobdispatch/server/database.asp, line 235

And the print out of the resulting string from the function is

(tblOrders.orderDate >= CONVERT(DATETIME, '2007-7-13', 102))

If i remove the Where clause the statement works fine..

Any ideas

View Replies !

RESOLVED: Stored Procedure Of Function?
I have a select statement like this. Notice that I am doing the same calculation on different fields. Is it possible I can make this, in coding terms, a fucntion; so I can call it when I like (this query) and just provide the field.

Example: select field1, test_field = secs_to_hhmmss(TALK_TIME) from ...

SELECT dbo.date_table.real_date, dbo.time_table.hh,
COUNT(dbo.CALLDETAIL.id) as NumOfCalls,
TalkTime =
CASE WHEN CAST(SUM(TALK_TIME) AS INTEGER)/3600<10 THEN '0' ELSE '' END
+ RTRIM(CAST(SUM(TALK_TIME) AS INTEGER)/3600)
+ ':' + RIGHT('0'+RTRIM((CAST(SUM(TALK_TIME) AS INTEGER) % 3600) / 60),2)
+ ':' + RIGHT('0'+RTRIM((CAST(SUM(TALK_TIME) AS INTEGER) % 3600) % 60),2),
HoldTime =
CASE WHEN CAST(SUM(HOLD_TIME) AS INTEGER)/3600<10 THEN '0' ELSE '' END
+ RTRIM(CAST(SUM(HOLD_TIME) AS INTEGER)/3600)
+ ':' + RIGHT('0'+RTRIM((CAST(SUM(HOLD_TIME) AS INTEGER) % 3600) / 60),2)
+ ':' + RIGHT('0'+RTRIM((CAST(SUM(HOLD_TIME) AS INTEGER) % 3600) % 60),2),

View Replies !

Trigger : Insert Maximum Primary ID
Hi,

via VBScript, I am inserting data into one table as below:
Code:

conn.Source = "INSERT INTO img (imageDesc,imageName,imageDate,imageUser,imageIP) VALUES ('"& ni &"','"& fn &"','"& Now() &"',"& Session("MM_UserID") &",'"& Request.ServerVariables("REMOTE_HOST") &"')"

In another table, I want to insert the primary key (imageID) of this newly inserted row into a new table called "t_image_Site" along with another value in another column.

Any advice/tutorials... this can be done with a trigger if I'm not mistaken?

JJ

View Replies !

Calculate Maximum Value Of Childrecords In A Trigger?
I have a parent record en some child records.

Let's say the child records have a field with a value in the range 1-9

Now I want to find the maximum value in the child records.

When I have that value, I want to write this value in a field in the parent record.

1) Should I do this in a (update) trigger?

2) How do you write such a trigger in sql-express?

thanks Klaas

Netherlands

View Replies !

Maximum Length Of System_user Function
I know that system_user can return different types of usernames based on the authentication method used to connect to the database. I am trying to nail down a standard field width for audit columns that store the return value from system_user but I can't find definitive information about the return type of the function. Does anyone know the maximum length of the return value from the system_user function and if its an nvarchar, varchar, nchar, etc.?

Thanks.

View Replies !

Using Trigger/Stored Procedure (Delete, Insert)?
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.

View Replies !

Create Trigger Or Stored Procedure In External DB?
I know you're not really supposed to do this, but I have a stored procedure that creates another database (using the tips in this thread: http://www.thescripts.com/forum/thread81377.html). It all works great, except that I need to also add some triggers and/or stored procedures in the new DB I'm creating. SQL Server does not seem too happy about me using a stored procedure to create these elements in an external database. Is there a way out of this or have I painted myself into a corner?

Thanks,
Alex

View Replies !

SQL Server 2005 Trigger Or Stored Procedure
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?

View Replies !

Disabling A Trigger From A Stored Procedure In Another Database
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...

View Replies !

Trigger Calling Extended Stored Procedure
Hello,

I've installed a "For Update" trigger for some table that calls a extended stored procedure (ESP). In some cases, the ESP needs to modify the same table, maybe causing a recursive call of the trigger.

Is there a way to "disable" the trigger inside the code just before the operation that updates the table and "re-enable" it just after? I know I can disable recursive triggering at database level, but I'm developing a database add-on so I don't want to interfere with the existing triggers behavior (if any) for the rest of the tables.

10x.

View Replies !