Hi guys,
I am really new to the Database triggers and procedures
and I just need you to recommend some links or books
that will explain them to me
coz I'm gonna have to use them soon.
Any help will be appreciated.
Thank you in advance.
is there a trigger that will be activated whena procedure is carried out?
View 13 Replies View Relatedwant to check, triggers are executed After the command is done right? not b4 isit? so if i want to check if the entry is to be Inserted, i should use SP instead? either that or i can use ROLLBACK right? how do i use ROLLBACK? finding Google also at the moment
View 6 Replies View RelatedDoes anyone know of or have a method for tracking stored procedures that get called by a trigger. I have inherited a project that when a user updates a row in a particular table, triggers get fired and result in a nesting error. Thanks for your help in advance.
View 1 Replies View RelatedHello,
Can I have a trigger call a stroed procedure and then in turn have the stored procedure call a COM object? If so how? Or where can I get the info to do it?
Thanks
Tony
tfeuz@sorcerysolutions.com
IS trigger also precompiled and stored in memory on server like SP?
Is there any advantage of converting a trigger into SP?
Thanks in advance
jfkuser
I need to create a procedure to output mean and deviation AND then I also need to create a trigger. Below is the table 'Account' I made.. but the procedure and trigger I made below that is not correct. Can someone revise this for me? I just can't figure it out.
Here is the 'Account' table I made earlier:
create table Account(Account_Number varchar(6)NOT NULL PRIMARY KEY, Branch_Name varchar(12)NOT NULL REFERENCES Branch (Branch_Name), Balance money)
insert into Account values(10101, 'First Bank', 5500)
insert into Account values(20202, 'US Bank', 3550)
insert into Account values(30303, 'Commerce', 7550)
-------------------------------------------------------------------------
Now I need to output the Mean and Standard Deviation of the above Account. So I tried this code but I'm not sure if its right since when I Analyze it, some values come out as 'null'.
CREATE PROCEDURE project2question2 AS
DECLARE @Mean MONEY
DECLARE @Deviation MONEY
SET @Mean = (SELECT avg(cast(Balance as float)) as mean from Account)
SET @Deviation = (SELECT Balance, STDEV(Balance) st_deviation
FROM Account
GROUP BY Balance)
-------------------------------------------------------------------------
The second question was to create a Trigger that would output the name "Account" and the time of change/update to it. So I had this so far, but I KNOW its not right, so can anyone revise this for me? I basically need to create a trigger named ActionHistory that would have two columns: TableName and ActionTime. They should tie to my Account table so that when it was changed/updated then the name 'Account' would appear under TableName in my trigger, and the datetime of update would appear under ActionTime. But I just can't figure this one out.
Create table ActionHistory(TableName varchar(12) NOT NULL, ActionTime datetime NOT NULL)
CREATE TRIGGER trigger_ex2
ON ActionHistory
AFTER INSERT, UPDATE
AS
BEGIN
UPDATE ActionHistory
SET Account = UPPER(LName) WHERE TableName in (SELECT TableName FROM ActionHistory)
SET Loan = UPPER(LName) WHERE IDN in (SELECT TableName FROM ActionHistory)
-------------------------------------------------------
If someone could give me these codes I would be very grateful. I know you guys are the SQL wizzards. Thanks. (by the way, I use SQL 2000 edition)
- SOnia
I have to write a script that would make prefixes for a word
for ex: Ragini Venkataraman ( is the string) is the record from one table and i need to store prefix "rag" and "ven" as two records in another table....
I need to know if this can be done using just SQL stored procedures or triggers...or if there needs to be some code(java code..we are using language java) written..
any help on how it can be done would be great....iam completely new to sql and it all seems confusing....
thanks
Hi folks,
Is there is any way to run a executable from triggers or stored procedures..
Please let me know, whether it is possible in SQl Server 7.0.
Let me know asap...
rgds,
VJ
I would like to know if the the DLL`s one can build with VB5/6 can be used to construct Extended Stored Procedures in MS SQLServer 6.5? If so, how does one do this. All the reference material I`ve come across is for C
Thanks in Advance - Ralph
I would like to know if the the DLL`s one can build with VB5/6 can be used to construct Extended Stored Procedures in MS SQLServer 6.5? If so, how does one do this. All the reference material I`ve come across is for C
Thanks in Advance - Ralph
Hi,
Is it possible to disable a trigger from a Stored Procedure? If it is, how do you do it?
Thanks,
Federico
For every trigger and stored procedure I have a try-catch that writes to an error_log table.
The problem is the inner error is not preserved, always get:
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
As seen below - though commented out:
I tried commiting any transactions - though I didn't create one.
I played with the
XACT_STATE though that was 0
My test case was last procedure has 1/0
Thanks,
Russ
-----------------------------------------------------------
Below is what I have
Step 1)
ALTER Trigger [trg_ActivityLogEntryReportsError] ON [dbo].[ActivityLog]
FOR INSERT AS
DECLARE @ActivityLogID int
,@AlertMessageTypeID int
,@comment nvarchar(max)
,@Error_Source nvarchar(max)
--- etc.
SELECT
@ActivityLogID = ActivityLogID
,@AlertMessageTypeID = AlertMessageTypeID
,@Comment = Comment
FROM INSERTED
BEGIN TRY
if @AlertMessageTypeID = 2 -- activity reported an error
begin
exec proc_CreateAlertLogEntry_forError
@ActivityLogID
,@Comment
update ActivityLog
set flgActivityChecked = 1
where @activityLogId = activityLogID
end
END TRY
BEGIN CATCH
select
@Error_Source = 'trg_ActivityLogEntryReportsError '
,@Error_Procedure = ERROR_Procedure()
--- etc.
INSERT INTO ERROR_LOG
( Error_Source
,Error_Procedure
,Error_Message
--- etc.
)
VALUES
(
@Error_Source
,@Error_Procedure
,@Error_Message
---etc.
,@Error_Comment )
-- if @@TRANCOUNT > 0
--begin
--commit
--end
END CATCH
Step 2)
/*
This will be called by a Trigger
*/
ALTER Procedure [dbo].[proc_CreateAlertLogEntry_forError]
(@ActivityLogID int
,@Comment nvarchar(max))
AS
Declare
@ProcessScheduleID int
,@ProcessID int
--,@comment nvarchar(max)
,@Error_Source nvarchar(max)
---etc
BEGIN TRY
insert into AlertLog
(
AlertMessageTypeID
,comment
,ActivityLogID
)
values
(
2 -- error
,@comment
,@ActivityLogID
)
end
END TRY
BEGIN CATCH
PRINT 'ERROR OCCURED'
PRINT ERROR_Procedure() + ' ' + ERROR_MESSAGE()
select
@Error_Source = 'proc_CreateAlertLogEntry_forError '
---etc.
INSERT INTO ERROR_LOG
( Error_Source
,Error_Procedure
,Error_Message
---etc.
)
VALUES
(
@Error_Source
,@Error_Procedure
,@Error_Message
--- etc.)
-- if @@TRANCOUNT > 0
--begin
--commit
--end
END CATCH
update ActivityLog
set
flgActivityChecked = 1
,UpdatedDate = getdate()
,UpdatedBy = suser_sname()
where
ActivityLogID = @ActivityLogID
STEP 3
ALTER Trigger [trg_AlertLogEntry_SendsOnInsert] ON [dbo].[AlertLog]
FOR INSERT AS
declare
@AlertLogID Int
,@AlertMessageTypeID int
,@Comment nvarchar(max)
,@Error_Source nvarchar(max)
,@Error_Procedure nvarchar(max)
,@Error_Message nvarchar(max)
--- etc.
SELECT
@AlertLogID = AlertLogID
,@AlertMessageTypeID = AlertMessageTypeID
,@Comment = isnull(Comment,'')
,@ActivityLogID = isnull(ActivityLogID,-1)
FROM INSERTED
BEGIN TRY
PRINT 'trg_AlertLogEntry_SendsOnInsert'
PRINT @COMMENT
exec proc_SendEmail
@AlertLogID
,@AlertMessageTypeID
,@comment
,@ActivityLogID
END TRY
BEGIN CATCH
select
@Error_Source = 'trg_AlertLogEntry_SendsOnInsert '
,@Error_Procedure = ERROR_Procedure()
,@Error_Message = ERROR_MESSAGE()
--- etc.
INSERT INTO ERROR_LOG
( Error_Source
,Error_Procedure
-- etc.)
VALUES
(
@Error_Source
,@Error_Procedure
,@Error_Message
---etc.)
-- if @@TRANCOUNT > 0
--begin
--commit
--end
END CATCH
STEP 4
ALTER Procedure [dbo].[proc_SendEmail]
(
@AlertLogID Int
,@AlertMessageTypeID int
,@Comment nvarchar(max) = ''
,@ActivityLogID int = -1
)
AS
declare @AlertSubject nvarchar(512)
,@AlertBody nvarchar(max)
,@myQuery nvarchar(512)
,@profile_name1 nvarchar(128)
,@return_value int
,@mymailitem int
,@Error_Source nvarchar(max)
---etc.
,@Error_Comment nvarchar(max)
,@Test int
/*
@return_value int -- not using at this point but 0 is OK 1 is failure
@mymailitem int -- not using now could store mailitem_id which is on msdb.dbo.sysmail_mailitems
sysmail_mailitems.sent_status could be either 0 new, not sent, 1 sent, 2 failure or 3 retry.
*/
select top 1 @profile_name1 = [name] from msdb.dbo.sysmail_profile
order by profile_id
set @profile_name1 = rtrim(ltrim(@profile_name1))
print '@profile_name1: ' + @profile_name1
print '@comment: ' + @comment
Declare @CrsrRecipient Cursor
BEGIN TRY
PRINT 'proc_SendEmail'
--set @test = 1/0 'test crashing
select
@AlertSubject = 'AlertSubject'
,@AlertBody = 'AlertBody'
,@recipients = 'russ@test.com'
print 'sending email ' + CAST(getdate() as nvarchar(100))
EXEC @return_value = msdb.dbo.sp_send_dbmail
@profile_name = @profile_name1
,@recipients = @EMAILID
,@body = @AlertBody
,@subject = @AlertSubject
,@mailitem_id = @mymailitem OUTPUT
print 'Done ' + CAST(getdate() as nvarchar(100))
print cast(@return_value as nvarchar(100))
update alertlog
set AlertSendStatusID = 1 --sent
where
@AlertLogID = AlertLogID
END TRY
BEGIN CATCH
PRINT 'ERROR OCCURED'
PRINT ERROR_Procedure() + ' ' + ERROR_MESSAGE()
select
@Error_Source = ' proc_SendEmail '
,@Error_Procedure = ERROR_Procedure()
--- etc.
INSERT INTO ERROR_LOG
( Error_Source
,Error_Procedure
---etc.)
VALUES
(
@Error_Source
,@Error_Procedure
,@Error_Message
--- etc.)
update alertlog
set AlertSendStatusID = 2 --error
where
@AlertLogID = AlertLogID
--if @@TRANCOUNT > 0
--begin
--commit
--end
END CATCH
Hi,
I would like to use the view of the company data in the following stored procedures
without needing to pass a value into the CompanyID identity column. Is there a way to do this? The following code contains the view, the insert trigger on the view, and the desired stored procedures.
Also, this code is given me problem as well the error message says it cannot convert varchar to int. This code is located in the insert trigger.
INSERT INTO State(StateName)
VALUES(@StateName)
Here is the code of the company view, notice the C.CompanyID it is used in the GetCompany stored procedure.
SELECT C.CompanyID, C.CompanyName, A.Street, A.City, S.StateName, A.ZipCode, A.Country, P.PhoneNumber
FROM Company AS C INNER JOIN
Address AS A ON C.AddressID = A.AddressID INNER JOIN
State AS S ON A.StateID = S.StateID INNER JOIN
Phone AS P ON C.PhoneID = P.PhoneID
Here is code for the insert trigger on the view
CREATE TRIGGER InsertTriggerOnCustomerView
ON ViewOfCompany
INSTEAD OF INSERT
AS
BEGIN
DECLARE @CompanyName VARCHAR(128)
DECLARE @AddressID INT
DECLARE @Street VARCHAR(256)
DECLARE @City VARCHAR(128)
DECLARE @StateID INT
DECLARE @StateName VARCHAR(128)
DECLARE @ZipCode INT
DECLARE @Country VARCHAR(128)
DECLARE @PhoneID INT
DECLARE @PhoneNumber VARCHAR(32)
--DECLARE @CompanyID INT
SELECT
--@CompanyID = CompanyID,
@CompanyName = CompanyName,
@Street = Street,
@City = City,
@StateName = StateName,
@ZipCode = ZipCode,
@Country = Country,
@PhoneNumber = PhoneNumber
FROM INSERTED
INSERT INTO State(StateName)
VALUES(@StateName)
SET @StateID = @@IDENTITY
INSERT INTO Address(Street, City, StateID, Country, ZipCode)
VALUES(@Street, @City, @StateID, @ZipCode, @Country)
SET @AddressID = SCOPE_IDENTITY()
INSERT INTO Phone(PhoneNumber)
VALUES(@PhoneNumber)
SET @PhoneID = SCOPE_IDENTITY()
INSERT INTO Company(CompanyName, AddressID, PhoneID)
VALUES(@CompanyName, @AddressID, @PhoneID)
END
The stored procedures are here.
CREATE PROCEDURE GetCompany
(
@CompanyID INT
)
AS
BEGIN
SELECT CompanyName,Street, City, StateName,
ZipCode, Country, PhoneNumber
FROM
ViewOfCompany
WHERE
CompanyID = @CompanyID
END
GO
CREATE PROCEDURE AddCompany
(
@CompanyName VARCHAR(128),
@Street VARCHAR(256),
@City VARCHAR(128),
@StateName VARCHAR(128),
@ZipCode VARCHAR(128),
@Country VARCHAR(128),
@PhoneNumber VARCHAR(32)
)
AS
BEGIN
INSERT INTO ViewOfCompany
VALUES(@CompanyName, @Street, @City, @StateName,
@ZipCode, @Country, @PhoneNumber)
END
GO
For those intersted here is our TOC and the book's link. You can preorder at this point. We are sticking to the Nov. timeframe, but we may get it done sooner.
Chapter 1 Introducing SQLCLR
Chapter 2 Building a Procedure
Chapter 3 SQLCLR Strucutre & Common Tasks
Chapter 4 Creating Objects
Chapter 5 Compare & Contrast
Chapter 6 Replacing TSQL Objects
Chapter 7 Using the Base Library
Chapter 8 Using Procedures in Apps
Chapter 9 Error Handling
Chapter 10 Administration
Chapter 11 Case Study
Here is the link:
http://www.wrox.com/WileyCDA/WroxTitle/productCd-0470054034.html
Enjoy,
Derek
How can i delete all user stored procedures and all table triggers very fastina single database?Thank you
View 17 Replies View RelatedThis isn€™t an problem as such, it€™s more of a debate.
If a table needs a number of update triggers which do differing tasks, should these triggers be separated out or encapsulated into one all encompassing trigger. Speaking in terms of performance, it doesn€™t make much of an improvement doing either depending upon the tasks performed. I was wondering in terms of maintenance and best practice etc. My view is that if the triggers do totally differing tasks they should be a trigger each on their own.
www.handleysonline.com
I'm trying to write a procedure that having created a new database,will then create within that new database all the tables andprocedures that go with it.In doing this I'm hitting the problem that you can't issue a USEcommand within a procedure.So my question is either- how do I get around this?- if I can't, how can I create procedures etc in a *different*(i.e. the newly created) databaseor- is there a better way to do all this (*)I have SQL files that do this currently, but I need to edit in thename of the database each time before execution, so I thought aprocedure would be better. Also I'd like eventually to expose someof this functionality via a web interface.Although I'm a newbie, I feel I'm diving in the deep end. Any goodpointers to all the issues involved in this aspect of databasemanagement would be appreciated.(*) One thought that occurs to me is to have a "template" database,and to then somehow copy all procedures, tables, view etc from that.--HTML-to-text and markup removal with Detaggerhttp://www.jafsoft.com/detagger/
View 10 Replies View Related
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
Thanks,
Jason
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
View 11 Replies View RelatedHi,
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Thank you in advance for any help on this matter
Is it possible to achieve this using triggers:When someone tries to delete a row in table A, the trigger should first delete a corresponding row in table B and then delete the row in table A. The reason being that, there is a foreign key set on Table B that references table A. So any attempt to delete a row in table A without deleting the corresponding row from B, throws an error.
View 1 Replies View RelatedHello All,
I have to write Trigger for Update,
I have two tables, one is for storing records of current values, and one is for storing history of values.
How to Write a Trigger on Main Table. As we have Inserted and Deleted Tables through which we can find Values, We dont have any Table for UPDATED Values.
Help me.
General Problem
I need to create a set of rows every time a new row is inserted into a table.
Example (I think this would work)...
select @insertedId = column1 from insertedselect @id = column1 from table1 where column2 in (select column1 from table2 where column2 = @insertedId)insert into table3 values(x, y, @id)
Is it possible to do the same kind of thing in a situation where the select statement returns multiple values and execute the insert statement for each of these values?
Also, if table3 was in fact the table on which the trigger acts, would it then be executed for every row created by the trigger?
Sorry if I sound confused. I am.
hi everybody..i tried to put thios loop in sql server 2000 But it is not taking The @ action taken value ,,it is only taking the default value of @actiontaken value.
SET @ActionTaken = 'A'
IF (@AType = 'A')IF @Status= 'O' IF (@KAppInd ='Y' AND @DAppInd=null)BEGINSET @ActionTaken = 'O'END
Please tell me other option in sql server 2000 for setting variable value based on conditions
Hi using triggers i try to insert some values in to my 2 tables: But its showing teh error as "The request for procedure 'Triginsert123s' failed because 'Triginsert123s' is a trigger object." This is my code in back end: sqlcon.Open() Dim cmd As New SqlCommand("Triginsert123s '" & txtID.Text & "','" & txtName.Text & "','" & txtRole.Text & "','" & txtDep.Text & "'", sqlcon) cmd.ExecuteNonQuery() sqlcon.Close() My trigger is: CREATE TRIGGER Triginsert123s ON [dbo].[EmpRole] FOR INSERT AS declare @Eid as tinyint, @Ename as varchar(50), @Role as char(10) Insert into Emprole(Eid,Ename,Role) values(@Eid,@Ename,@Role) insert into empdep(eid,dep) values(@eid,@Role) Whats the probs?, Plz i am new to triggers help me,
View 3 Replies View RelatedHi All,
I'm using triggers to handle my transaction log to cature inserts and updates. It works fine except if the user clicks on the Save button more than once, the trigger is fired and the record is written to the log even if the record wasn't changed. Does anyone know how to check if the record was actually changed so that it isn't written to the table if it wasn't?
Thank you,
Mike
When I execute a stored proc from my asp.net page, will the results of a trigger be returned to my program?
For instance say my stored proc is:
Update Employees set
(Lastname = @Lastname)
where ID = @ID
And my trigger is:
CREATE TRIGGER tr_Employees_U on Employees FOR UPDATE AS
IF UPDATE(lastname)
BEGIN
RAISERROR ('cannot change lastname', 16, 1)
ROLLBACK TRAN
RETURN
END
GO
It seems like since this is an AFTER trigger that my webpage would actually get a valid return code from my stored procedure however the trigger would rollback those changes correct? Or would the trigger get fired and send it's return code to my webpage?
I'm trying to write an instead of trigger for a view in SqlExpress...the table and views are defined as such:CREATE TABLE [dbo].[Work]( [WorkID] [int] IDENTITY(1,1) Primary Key, [ResourceID] [int] NOT NULL, [TaskID] [int] NOT NULL, [WorkDate] [datetime] NOT NULL, [WorkQuantity] [float] NOT NULL, [IsEstimate] [bit] NOT NULL DEFAULT ((0)), [Project] [int] NOT NULL,);CREATE VIEW [dbo].[ActualWork]ASSELECT WorkID, ResourceID, TaskID, WorkDate, WorkQuantity, ProjectFROM dbo.[Work]WHERE (IsEstimate = 0);CREATE VIEW [dbo].[EstimatedWork]ASSELECT WorkID, ResourceID, TaskID, WorkDate, WorkQuantity, ProjectFROM dbo.[Work]WHERE (IsEstimate = 1);Given that, what is wrong with the following create trigger statement:Create Trigger trg_InsertActualWork ondbo.ActualWork Instead of InsertasBEGIN Insert into dbo.Work( ResourceID, TaskID, Project, WorkDate, WorkQuantity, IsEstimate ) values ( inserted.ResourceID, inserted.TaskID, inserted.Project, inserted.WorkDate, inserted.WorkQuantity, 0 );END
View 3 Replies View Relatedl'm trying to build a trigger on a table. The reason for the trigger is to check a certain field for the first three characters if it has ie abc it must update another field in this case loanbook to newabc.How do l write the trigger so that it also check if exists and perform the updates. Please help its Urgent. l've listed the trigger below.
CREATE TRIGGER UpdTest_TRGData
ON Test_TRG
FOR insert,Update AS
IF left('Loan_No',3)='ABC'
update Test_TRG
set loanbook = 'NEWABC'
else
IF left('Loan_No',3)='DEF'
update Test_TRG set
loanbook = 'NEWDEF'
where loanbook is null
else
update Test_TRG
set loanbook =left('Loan_No',3)
where loanbook is null
I'm a bit confused on this bit please elaborate : " FROM Test_TRG t INNER JOIN inserted i ON t.PK = i.PK ".
The PK is on which field? Basically this trigger should ensure that on insertion of o new loan if
The left(loan_no,3)=MCG and its null then NEWMCG
left(loan_no,3)=MCG and its null then NEWMCG
left(loan_no,3)=KVS and its null then NEWKVS
left(loan_no,3)=MFS and its null then MFS
left(loan_no,3)=TCR and its null then TCR
left(loan_no,3)=ABL and its null then ABL
Listed below is what l've tried to do but l'm missing the PK part.Otherwise everything else you explained
in the script is clear. Thanks man its urgent. When l parse the query its fine , but When l run it
l get an error.
CREATE TRIGGER UpdTest_TRGData
ON Test_TRG
FOR INSERT, UPDATE
AS
UPDATE Test_TRG
SET LoanBook = CASE WHEN LEFT( i.Loan_No, 3 ) = 'MCG' THEN 'NewMCG'
WHEN LEFT( i.Loan_No, 3 ) = 'KVS' AND i.LoanBook IS NULL THEN 'NewKVS'
WHEN LEFT( i.Loan_No, 3 ) = 'MFS' AND i.LoanBook IS NULL THEN 'MFS'
WHEN LEFT( i.Loan_No, 3 ) = 'ABL' AND i.LoanBook IS NULL THEN 'ABL'
WHEN i.LoanBook IS NULL THEN LEFT( i.Loan_No, 3 )
END
FROM Test_TRG t INNER JOIN inserted i ON t.PK = i.PK
-- Fill in Primary Key or other Join Column(s)
WHERE LEFT( i.Loan_No, 3 ) = 'MCG'
OR ( LEFT( i.Loan_No, 3 ) = 'KVS' AND i.LoanBook IS NULL )
OR i.LoanBook IS NULL
====== Error message =================
Server: Msg 207, Level 16, State 3, Procedure UpdTest_TRGData, Line 11
Invalid column name 'PK'.
Server: Msg 207, Level 16, State 1, Procedure UpdTest_TRGData, Line 11
Invalid column name 'PK'.
I an loading records from a flat file into a table, which is done everyday by a scheduled job in SQL Server 7.0.
How can I make sure that if the job is run twice in a day for some reason that the same rows are not inserted into the table again? Do I have to write a insert trigger on the table ??? If so how can I achive the objective ??
Hi! i have a simple question...is possible to create a trigger who affect multiple tables?
The idea will be something like this:
create trigger mytrigger
on sales, users
as...
of course, this don´t work :)