i am having an issue with my trigger code
i believe that i should be able to use the multiple if else statements
and i just threw in an update in each begin end statement
but when i check the syntax it fails
any reason why that is
CREATE TRIGGER [tr_update_conveyor_interface] ON [dbo].[t_conveyor_interface]
FOR UPDATE
AS
Hi, Am having problems creating trigger. I want to have a trigger update a table (tblClaimItemID) with the value of ClaimItemID + 1 and insert this value as the primary key of tblClaimItemsDaily(to field ClaimItemID) on insert of a record into tblClaimItemsDaily (so that when data is appended to tblClaimsItems we have a unique primary key). tblClaimItemsDaily is the day sheet, so need to have another table generate the primary key since there will be multiple tblClaimItemsDaily tables open at any given time, but the key must be unique. I've been looking at deja and in my books, but they don't seem to indicate how to do this.
Here's the code. Any ideas? ------------begin code-------
CREATE TRIGGER trgClaimItemID ON [tblClaimItemsDaily] FOR INSERT,UPDATE AS
DECLARE @Item int
IF UPDATE(ClaimID) BEGIN IF @@ROWCOUNT = 1 Update dbo.tblClaimItemID SET dbo.tblClaimItemID.ClaimItemID = dbo.tblClaimItemID.ClaimItemID + 1 FROM dbo.tblClaimItemID Select @Item = dbo.tblClaimItemID.ClaimItemID FROM dbo.tblClaimItemID
-------------------------------- CREATE TRIGGER trIns_Test ON tblTest FOR INSERT AS
DECLARE @Message VARCHAR(100), @Num INT
SELECT @Num = user_id from INSERTED SELECT @Message = 'NET SEND NICK '+' Here is the new # - ' + CONVERT(VARCHAR(100), @Num) EXEC master.dbo.xp_cmdshell @Message --------------------------------
But when i insert a row into tblTest table i'am supposed to receive a NET SEND message. But its not happening if i hardcode the value @Num i.e
SELECT @Num = 321
Then i receive a message, but why this INSERTED clause does't work i'm unable to trace out.
Hi, I am trying to concatenate the columns (PrevEmp01, PrevEmp02, PrevEmp03, PrevEmp04, PrevEmp05) into column (ft) using trigger: CREATE TRIGGER [tg_prevemp_ft_update] ON [tStaffDir_PrevEmp] FOR INSERT, UPDATEASUPDATE tStaffDir_PrevEmp SET ft = PrevEmp01 + ' ' + PrevEmp02 + ' ' + PrevEmp03 + ' ' + PrevEmp04 + ' ' + PrevEmp05 I would expect the (ft) column will be populated accordingly regardless if any of the columns are (Null).But the Trigger will only work when all the 5 columns are populated. If one of the column is (Null), the (ft) column will be (Null) too.Please advise. Many Thanks.
--If Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted) set @var_db_contract =(SELECT a.db_contract FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) IF @var_db_contract IS NOT NULL BEGIN SET @var_db_contract=(SELECT a.db_contract FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) SET @var_cqe=(SELECT a.cqe_numb FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) SET @var_pc=(SELECT a.pc_code FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) SET @var_item=(SELECT a.item_no FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) SET @var_fiyr=(SELECT a.fy_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) set @var_chk ="Y" END ELSE BEGIN SET @var_db_contract=(SELECT a.db_contract FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) SET @var_cqe=(SELECT a.cqe_numb FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) SET @var_pc=(SELECT a.pc_code FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) SET @var_item=(SELECT a.item_no FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) SET @var_fiyr=(SELECT b.fy_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) set @var_chk="N" END SET @var_amt_paid=(SELECT a.amt_paid_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) SET @var_amt_old=(SELECT b.amt_paid_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) SET @var_amt_result =ISNULL(@var_amt_paid,0) - ISNULL(@var_amt_old,0)
SET @var_amt_ret = (SELECT a.amt_ret_item from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no) SET @var_amt_ret_old=(SELECT b.amt_ret_item from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no) SET @var_amt_ret_result = isnull(@var_amt_ret,0) - isnull(@var_amt_ret_old,0)
SET @var_quant_new = (SELECT a.quantity from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no) SET @var_quant_old =(SELECT b.quantity from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no) SET @var_quant_result = isnull(@var_quant_new,0) - isnull(@var_quant_old,0) SELECT @item_new = new_item FROM VALID_ITEM WHERE DB_CONTRACT = @var_db_contract AND PC_CODE = @var_PC AND ITEM_NO = @var_ITEM
UPDATE ae_contract set amt_paid_contr = isnull(amt_paid_contr,0) +@var_amt_result, amt_ret_contr = isnull(amt_ret_contr,0) + @var_amt_ret_result where db_contract = @var_db_contract
IF @item_new = 'N' BEGIN update vendor set used_amt = isnull(used_amt,0) + @var_amt_result + @var_amt_ret_result where db_vendor = (select gen_contr from ae_contract where ae_contract.db_contract=@var_db_contract); END UPDATE enc_det set amt_paid_fy = isnull(amt_paid_fy,0) + @var_amt_result, amt_ret_fy = isnull(amt_ret_fy,0) + @var_amt_ret_result where db_contract = @var_db_contract and pc_code = @var_pc and fy = @var_fiyr
UPDATE valid_item set tamt_ret_item = isnull(tamt_ret_item,0) + @var_amt_ret_result, tamt_paid_item = isnull(tamt_paid_item,0) + @var_amt_result, qtd = isnull(qtd,0) + @var_quant_result where db_contract = @var_db_contract and pc_code = @var_pc and item_no = @var_item
Hi Experts, The trigger is not sucessfully calling the Stored Procedure. This was working up until last year (12/21/2007 @ 1600 hrs.)Here I am providing codes for both Trigger and Storedprocedure.Can you help me out where the problem is?
Trigger
USE [personnelreq] GO /****** Object: Trigger [tgrTracking] Script Date: 01/16/2008 11:55:29 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO
/***************************************************************************************************************************************************************** * Trigger Name: tgrTracking * Description: Calls the CalcTrackingHours stored procedure for replacement and net staff addition * requisitions when the requisitions are emailed, transferred, or completed. * History * TKT/CO # Date Developer Description * ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ * 01/03/2005 Zsofia Horompoli Created * CO#1570 03/22/2005 Zsofia Horompoli Added originator email tracking for transfer events *****************************************************************************************************************************************************************/ CREATE TRIGGER [tgrTracking] ON [dbo].[Log2] FOR INSERT AS
DECLARE @reqId int -- store requisition ID DECLARE @eventId int -- event value inserted DECLARE @parentId int -- store parent requisition ID DECLARE @eventTime datetime -- store date/time of event DECLARE @email varchar(50) -- store email address DECLARE @logId int -- store ID assigned to the record in the Log2 table DECLARE @location varchar(30) -- Location DECLARE @title varchar(30) -- Job title DECLARE @multiReq int -- > 0 = Multi NSA DECLARE @origMail varchar(50) -- Originator's email address DECLARE @subject varchar(200) -- Email subject line DECLARE @from varchar(50) -- From email DECLARE @body varchar(8000) -- Email body DECLARE @reqType int -- Requisition type (0-replacement, 1-net staff addition, 6-Bonus) DECLARE @sReqType varchar(30) -- Requisition type description DECLARE @appEmail varchar(500) -- Approvers' email addresses
SELECT @from = 'compensationandbenefits@west.com'
DECLARE @inserted_rows AS CURSOR -- hold the inserted rows SET @inserted_rows = CURSOR FOR SELECT id,reqid, timestamp, event, stuff(notes,1,patindex('%:%',notes),'') AS email FROM inserted -- get the Log2 id,requisition id, event, timestamp, and email address from the updated/inserted rows
OPEN @inserted_rows FETCH NEXT FROM @inserted_rows INTO @logId, @reqId, @eventTime, @eventId, @email WHILE (@@FETCH_STATUS = 0) BEGIN --Is this an email/transfer/complete/deny event? IF (@eventId = 4 OR @eventId = 6 OR @eventId = 7 OR @eventId = 0 OR @eventId = 10) BEGIN -- Yes, is this a deny event? IF (@eventId = 0) BEGIN --Yes, is this an active replacement/net staff addition/parent multi NSA requisition?
SELECT @parentId = id FROM reqs2 r WHERE requisitiontype in (0,1) and id = @reqId and (SELECT ParentReqId from ParentChild WHERE ChildReqId = @reqId) IS NULL and NOT (markedfortransfer = 0 AND logging <> 1) AND markedfortransfer <> 2 END ELSE IF (@eventId = 6) BEGIN SELECT @parentId = id FROM reqs2 r WHERE requisitiontype IN (0,1,6) AND id = @reqId AND (SELECT ParentReqId FROM ParentChild WHERE ChildReqId = @reqId) IS NULL and NOT (markedfortransfer = 0 AND logging <> 1) and (SELECT id FROM Log2 WHERE reqId = @reqId AND event = 0) IS NULL END ELSE IF (@eventId = 7 OR @eventId = 10) BEGIN SELECT @parentId = id FROM reqs2 r WHERE requisitiontype IN (0,1) AND id = @reqId AND (SELECT ParentReqId FROM ParentChild WHERE ChildReqId = @reqId) IS NULL and markedfortransfer <> 2 and (SELECT id FROM Log2 WHERE reqId = @reqId AND event = 0) IS NULL END ELSE BEGIN -- No, is this an active replacement/net staff addition/parent multi NSA requisition? SELECT @parentId = id FROM reqs2 r WHERE requisitiontype IN (0,1) AND id = @reqId AND (SELECT ParentReqId FROM ParentChild WHERE ChildReqId = @reqId) IS NULL and NOT (markedfortransfer = 0 AND logging <> 1) AND markedfortransfer <> 2 and (SELECT id FROM Log2 WHERE reqId = @reqId AND event = 0) IS NULL END
--Is this a requisition to be tracked? IF @parentId is not NULL BEGIN --Yes, is this a transfer event? IF @eventId = 6 BEGIN --Yes, retrieve information for email SELECT @location = RTRIM(L.DESCR), @title = RTRIM(PT.DESCR), @multiReq = (SELECT COUNT(*) FROM ParentChild WHERE ParentReqID = R.id), @reqType = R.requisitiontype FROM reqs2 R LEFT OUTER JOIN TDSdev.dbo.PS_LOCATION_TBL L ON R.sitename = L.LOCATION LEFT OUTER JOIN TDSdev.dbo.PS_JOBCODE_TBL PT ON R.jobtitle = PT.JOBCODE WHERE R.id = @reqId
-- Get email addresses to use EXEC sel_EmailUsers @reqId = @reqId, @bApprover = 1, @currentUser = NULL, @origEmail = @origMail OUTPUT, @appEmail = @appEmail OUTPUT
--Set requisition type description IF @reqType = 0 BEGIN --Replacement requisition SELECT @sReqType = 'Replacement' END ELSE IF @reqType = 1 AND @multiReq > 0 BEGIN --Multi-NSA SELECT @sReqType = 'Multiple Net Staff Addition' END ELSE IF @reqType = 1 BEGIN --NSA SELECT @sReqType = 'Net Staff Addition' END ELSE IF @reqType = 6 BEGIN --Bonus SELECT @sReqType = 'Bonus' END
--Build subject line --Do we have a location? IF @location IS NULL BEGIN --No, default to Nothing SELECT @location = '' END
--Do we have a job title? IF @title IS NULL BEGIN --No, default to Nothing SELECT @title = '' END
--Set email body IF @reqType = 6 -- If Bonus Req BEGIN SELECT @body = '<p>Bonus requisition number ' + CAST(@reqId AS varchar(15)) + ' has been processed by Compensation.</p>' SELECT @body = @body + '<p>If you have any questions, please contact us at <a href=''mailto: compensationandbenefits@west.com'' target=''_blank''>compensationandbenefits@west.com</a> .</p>' END ELSE IF @reqType IN (0,1) -- If Net Staff or Replacement Req BEGIN SELECT @body = '<p>Requisition number ' + CAST(@reqId AS varchar(15)) + ' has been finalized and sent to Human Resources on ' + convert(char(10),@eventTime, 101) + ' at ' + substring(convert(char(19),@eventTime, 100), 12, 8) + ' for recruiting.</p>' SELECT @body = @body + '<p>If you have any questions, please contact us at <a href=''mailto: compensationandbenefits@west.com'' target=''_blank''>compensationandbenefits@west.com</a> .</p>' END
-- Call the CalcTrackingHours stored procedure IF @reqType IN (0,1) BEGIN EXEC CalcTrackingHours @event = @eventId, @reqId = @reqId, @emailTo = @email, @endDateTime = @eventTime, @logId = @logId END END END FETCH NEXT FROM @inserted_rows INTO @logId, @reqId, @eventTime, @eventId, @email END
--clean up CLOSE @inserted_rows DEALLOCATE @inserted_rows
Stored Procedure is
USE [personnelreq] GO /****** Object: StoredProcedure [dbo].[CalcTrackingHours] Script Date: 01/16/2008 12:01:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO /****** Object: Stored Procedure dbo.CalcTrackingHours Script Date: 4/12/2005 9:20:19 AM ******/
/***************************************************************************************************************************************************************** * Stored Procedure Name: CalcTrackingHours * Description: Determines the appropriate tracking level, the number of business hours the requisition * spent at the current tracking level and inserts this information into the timeLog table * History * Date Developer Description * ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ * 01/03/2005 syszxh Created *****************************************************************************************************************************************************************/ CREATE procedure [dbo].[CalcTrackingHours] @event int, -- 4=Emailed,6=Transferred,7=Completed @reqId int, -- Requisition Id @emailTo varchar(50), -- Email address requisition was forwarded to @endDateTime datetime, -- Date/time of event @logId int -- ID assigned to new record in the Log2 table as set nocount on
declare @currentUserType as int -- Current user type declare @startDateTime as datetime -- Date/time requisition arrived at current step declare @newUserType as int -- New user type declare @currentTrackLevel as varchar(5) -- Tracking level to be inserted into the timeLog table declare @newTrackLevel as varchar(5) -- New tracking level declare @trackEmail as varchar(50) -- Email address at current tracking level declare @count as int -- counter variable declare @errorValue as int -- Error flag (1 = start date/time missing) declare @trackHours as decimal(10,2)
-- Retrieve last email's user type, date/time stamp, and email address select top 1 @currentUserType = userType, @startDateTime = timestamp, @trackEmail = n.userid from personnelreq.dbo.Log2 l left outer join personnelreq.dbo.names2 n on stuff(notes,1,patindex('%:%',notes),'') = n.userid where (l.event = 4 or l.event = 10) and timestamp <= @endDateTime and reqid = @reqId and l.id < @logId order by l.id desc
-- Last email found? if @startDateTime is NULL begin -- No, this is the very first email, retrieve new user type select @newUserType = userType from personnelreq.dbo.names2 n where userid = @emailTo
-- Is new user @C&B? if @newUserType = 2 begin -- Yes, set new tracking level select @newTrackLevel = 'cb1' end else begin -- No, set new tracking level select @newTrackLevel = 'dept' end end else begin -- Is this event = email? if @event = 4 begin -- Retrieve new user type select @newUserType = userType from personnelreq.dbo.names2 n where userid = @emailTo
-- Is the current user the same as the new user? if @currentUserType = @newUserType and @emailTo = @trackEmail begin -- Yes, get latest level select top 1 @newTrackLevel = trackLevel from timeLog where reqID = @reqId and trackEmail = @trackEmail and dateIn = @startDateTime order by dateIn desc
select @currentTrackLevel = @newTrackLevel end -- Is the current user type @C&B? else if @currentUserType <> 2 or @currentUserType is NULL begin -- No, retrieve current tracking level exec GetTrackLevel @userType = @currentUserType, @reqID = @reqID, @email = @trackEmail, @trackLevel = @currentTrackLevel output -- Is the new user type @C&B? if @newUserType = 2 begin -- Yes, retrieve new tracking level exec GetCBLevel @trackLevel = @currentTrackLevel, @reqID = @reqID, @currentTrackLevel = @newTrackLevel output end else -- No, retrieve new tracking level begin exec GetTrackLevel @userType = @newUserType, @reqID = @reqID, @email = @emailTo, @trackLevel = @newTrackLevel output end end else begin -- Yes, retrieve new tracking level exec GetTrackLevel @userType = @newUserType, @reqID = @reqID, @email = @emailTo, @trackLevel = @newTrackLevel output -- Retrieve current tracking level exec GetCBLevel @trackLevel = @newTrackLevel, @reqID = @reqID, @currentTrackLevel = @currentTrackLevel output end end -- Is this event = transfer/completed? else if @event = 6 or @event = 7 or @event = 10 begin -- No, any existing entries for exec3? if (select count(*) from timeLog where trackLevel = 'exec3' and reqID = @reqID) > 0 begin -- Yes, set current tracking level to cb5 select @currentTrackLevel = 'cb5' end -- Any existing entries for exec2? else if (select count(*) from timeLog where trackLevel = 'exec2' and reqID = @reqID) > 0 begin -- Yes, set current tracking level to cb4 select @currentTrackLevel = 'cb4' end -- Any existing entries for exec1? else if (select count(*) from timeLog where trackLevel = 'exec1' and reqID = @reqID) > 0 begin -- Yes, set current tracking level to cb3 select @currentTrackLevel = 'cb3' end -- Any existing entries for acct? else if (select count(*) from timeLog where trackLevel = 'acct' and reqID = @reqID) > 0 begin -- Yes, set current tracking level to cb2 select @currentTrackLevel = 'cb2' end else begin -- Set current tracking level to cb1 select @currentTrackLevel = 'cb1' end end
-- Calculate hours worked if @event <> 10 begin exec CalcBusinessHours @startDateT = @startDateTime, @endDateT = @endDateTime, @totalHours = @trackHours output
-- Update information in the timeLog table update timeLog set dateOut = @endDateTime, trackHours = @trackHours where reqID = @reqID and trackHours = 0 and dateOut = '1990-12-31' end end
-- Is this event = email? if @event = 4 and @newTrackLevel is not NULL begin -- Yes, insert new row for new track step insert into timeLog (reqID, trackLevel, dateIn, trackEmail, trackHours, dateOut) values (@reqID, @newTrackLevel, @endDateTime, @emailTo, 0, '1990-12-31') end -- Is this event = reopen? else if @event = 10 and @trackEmail is not NULL begin -- Yes, insert new row for new track step insert into timeLog (reqID, trackLevel, dateIn, trackEmail, trackHours, dateOut) values (@reqID, @currentTrackLevel, @endDateTime, @trackEmail, 0, '1990-12-31') end
i have this trigger in my database : ALTER TRIGGER dbo.AddVoucher ON dbo.User_AddVoucher AFTER INSERTAS SET NOCOUNT ON;DECLARE @UserId int, @Add_id int, @voucher_id char, @Kredit money, @date smalldatetime, @last_balance money, @voucher_status charSELECT @UserId = UserId, @voucher_id = Voucher_ID, @Add_id = Add_id, @date = Deposit_Date FROM InsertedSELECT @Kredit= Voucher_Value, @voucher_status = Voucher_Status FROM Voucher WHERE Voucher_ID = @voucher_idINSERT INTO User_Balance(AddVoucher_ID, UserId, Update_Type, Update_Date) VALUES (@Add_id,@UserId, 'Kredit',@date)select @last_balance = Balance from User_Balance WHERE UserId = @UserId and Balance = (select TOP 1 Balance User_Balance where UserId = @UserId order by Update_Id DESC) if (@voucher_status = 'active') -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. -- Insert statements for trigger hereBEGIN update User_Balance set Balance = @last_balance + @Kredit where AddVoucher_ID = @Add_id update Voucher set Sold_Date = @date where Voucher_ID = @voucher_idENDELSE BEGIN raiserror ('Voucher is not valid',0,1) rollback transaction ENDgo the problem is the update function is not working and the if statement always put to 'FALSE' do you think anything wrong with the code
We've altered an UPDATE trigger - now it doesn't work properly. When updating a record (via a web application) by clearing the value for a particlar column, we get error:
UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.
Code = 800a0c93 Source = ADODB.Recordset Description = Operation is not allowed in this context.
What the trigger does is prevent a zero-length string from being inserted into the database, which violates a check constraint, and instead inserts NULL. The web app - whose code we cannot modify - apparently does this. This was working fine, until we altered the trigger to add an additional column for update. Our SQL developer person has left, so us less knowledgable (about SQL) folks are trying to pick up the slack.
trigger code:
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
ALTER TRIGGER trigViaNetCardholderUpdateEmptyStrings ON viewViaNetCardholder INSTEAD OF UPDATE AS UPDATE tblIDCross SET tblIDCross.chUSCId = CASE WHEN ins.chUSCId = '' THEN NULL ELSE ins.chUSCId END, tblIDCross.chNineDigit = CASE WHEN ins.chNineDigit = '' THEN NULL ELSE ins.chNineDigit END, tblIDCross.chPID = CASE WHEN ins.chPID = '' THEN NULL ELSE ins.chPID END, tblIDCross.chEmployeeId = CASE WHEN ins.chEmployeeId = '' THEN NULL ELSE ins.chEmployeeId END, tblIDCross.chAIMSNumber = CASE WHEN ins.chAIMSNumber = '' THEN NULL ELSE ins.chAIMSNumber END, tblIDCross.intCustomerType = ins.intCustomerType FROM INSERTED ins WHERE tblIDCross.intUSCardId = ins.intUSCardId
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
I am trying to important some data in the an existing table with trigger. Unfortunately, the data were successfully inserted into the said table but the trigger did not work out. Why? How to do the tricks?
Input : 1) UPDATE TBL_SO SET QTY='10',Price='100' Where SONo='10' 2)UPDATE TBL_SO SET QTY='10',Price='100' Â Total=Qty*Price Where SONo='10'
Output: What I get? I tried both 1 and 2, It(Query) does not work on first time,I executed (Query) second time It works.Why?What I need?  How to work on First Time?
I had a view in which I did something like this isnull(fld,val) as 'alias'
when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005. When I change the query to fld as 'alias' then it works ok in sql 2005 . why ?? I still have sql 2000 (8.0) compatability.
Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???
any clues or answers ?? it is some configuration issue ?
I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...
This was my test:
Dim cmd As New SqlCommand("pContact_Update", cn) 'Dim cmd As New SqlCommand("pContact_Add", cn)
Catch ex As Exception Label1.Text = ex.Message End Try
When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.
I have looked at the stored procedures and the syntax is correct according to SQL Server.
I'm new to this whole SQL Server 2005 thing as well as database design and I've read up on various ways I can integrate business constraints into my database. I'm not sure which way applies to me, but I could use a helping hand in the right direction.
A quick explanation of the various tables I'm dealing with: WBS - the Work Breakdown Structure, for example: A - Widget 1, AA - Widget 1 Subsystem 1, and etc. Impacts - the Risk or Opportunity impacts for the weights of a part/assembly. (See Assemblies have Impacts below) Allocations - the review of the product in question, say Widget 1, in terms of various weight totals, including all parts. Example - September allocation, Initial Demo allocation, etc. Mostly used for weight history and trending Parts - There are hundreds of Parts which will eventually lead to thousands. Each part has a WBS element. [Seems redundant, but parts are managed in-house, and WBS elements are cross-company and issued by the Government] Parts have Allocations - For weight history and trending (see Allocations). Example, Nut 17 can have a September 1st allocation, a September 5th allocation, etc. Assemblies - Parts are assemblies by themselves and can belong to multiple assemblies. Now, there can be multiple parts on a product, say, an unmanned ground vehicle (UGV), and so those parts can belong to a higher "assembly" [For example, there can be 3 Nut 17's (lower assembly) on Widget 1 Subsystem 2 (higher assembly) and 4 more on Widget 1 Subsystem 5, etc.]. What I'm concerned about is ensuring that the weight roll-ups are accurate for all of the assemblies. Assemblies have Impacts - There is a risk and opportunity impact setup modeled into this design to allow for a risk or opportunity to be marked on a per-assembly level. That's all this table represents.
A part is allocated a weight and then assigned to an assembly. The Assemblies table holds this hierarchical information - the lower assembly and the higher one, both of which are Parts entries in the [Parts have Allocations] table.
Therefore, to ensure proper weight roll ups in the [Parts have Allocations] table on a per part-basis, I would like to check for any inserts, updates, deletes on both the [Parts have Allocations] table as well as the [Assemblies] table and then re-calculate the weight roll up for every assembly. Now, I'm not sure if this is a huge performance hog, but I do need to keep all the information as up-to-date and as accurate as possible. As such, I'm not sure which method is even correct, although it seems an AFTER DML trigger is in order (from what I've gathered thus far). Keep in mind, this trigger needs to go through and check every WBS or Part and then go through and check all of it's associated assemblies and then ensure the weights are correct by re-summing the weights listed.
If you need the design or create script (table layout), please let me know.
Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'
This Audit Trigger is Generic (i.e. non-"Table Specific") attach it to any tabel and it should work. Be sure and create the 'Audit' table first though.
The following code write audit entries to a Table called 'Audit' with columns 'ActionType' //varchar 'TableName' //varchar 'PK' //varchar 'FieldName' //varchar 'OldValue' //varchar 'NewValue' //varchar 'ChangeDateTime' //datetime 'ChangeBy' //varchar
using System; using System.Data; using System.Data.SqlClient; using Microsoft.SqlServer.Server;
public partial class Triggers { //A Generic Trigger for Insert, Update and Delete Actions on any Table [Microsoft.SqlServer.Server.SqlTrigger(Name = "AuditTrigger", Event = "FOR INSERT, UPDATE, DELETE")]
public static void AuditTrigger() { SqlTriggerContext tcontext = SqlContext.TriggerContext; //Trigger Context string TName; //Where we store the Altered Table's Name string User; //Where we will store the Database Username DataRow iRow; //DataRow to hold the inserted values DataRow dRow; //DataRow to how the deleted/overwritten values DataRow aRow; //Audit DataRow to build our Audit entry with string PKString; //Will temporarily store the Primary Key Column Names and Values here using (SqlConnection conn = new SqlConnection("context connection=true"))//Our Connection { conn.Open();//Open the Connection //Build the AuditAdapter and Mathcing Table SqlDataAdapter AuditAdapter = new SqlDataAdapter("SELECT * FROM Audit WHERE 1=0", conn); DataTable AuditTable = new DataTable(); AuditAdapter.FillSchema(AuditTable, SchemaType.Source); SqlCommandBuilder AuditCommandBuilder = new SqlCommandBuilder(AuditAdapter);//Populates the Insert command for us //Get the inserted values SqlDataAdapter Loader = new SqlDataAdapter("SELECT * from INSERTED", conn); DataTable inserted = new DataTable(); Loader.Fill(inserted); //Get the deleted and/or overwritten values Loader.SelectCommand.CommandText = "SELECT * from DELETED"; DataTable deleted = new DataTable(); Loader.Fill(deleted); //Retrieve the Name of the Table that currently has a lock from the executing command(i.e. the one that caused this trigger to fire) SqlCommand cmd = new SqlCommand("SELECT object_name(resource_associated_entity_id) FROM ys.dm_tran_locks WHERE request_session_id = @@spid and resource_type = 'OBJECT'", conn); TName = cmd.ExecuteScalar().ToString(); //Retrieve the UserName of the current Database User SqlCommand curUserCommand = new SqlCommand("SELECT system_user", conn); User = curUserCommand.ExecuteScalar().ToString(); //Adapted the following command from a T-SQL audit trigger by Nigel Rivett //http://www.nigelrivett.net/AuditTrailTrigger.html SqlDataAdapter PKTableAdapter = new SqlDataAdapter(@"SELECT c.COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk , INFORMATION_SCHEMA.KEY_COLUMN_USAGE c where pk.TABLE_NAME = '" + TName + @"' and CONSTRAINT_TYPE = 'PRIMARY KEY' and c.TABLE_NAME = pk.TABLE_NAME and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME", conn); DataTable PKTable = new DataTable(); PKTableAdapter.Fill(PKTable);
switch (tcontext.TriggerAction)//Switch on the Action occuring on the Table { case TriggerAction.Update: iRow = inserted.Rows[0];//Get the inserted values in row form dRow = deleted.Rows[0];//Get the overwritten values in row form PKString = PKStringBuilder(PKTable, iRow);//the the Primary Keys and There values as a string foreach (DataColumn column in inserted.Columns)//Walk through all possible Table Columns { if (!iRow[column.Ordinal].Equals(dRow[column.Ordinal]))//If value changed { //Build an Audit Entry aRow = AuditTable.NewRow(); aRow["ActionType"] = "U";//U for Update aRow["TableName"] = TName; aRow["PK"] = PKString; aRow["FieldName"] = column.ColumnName; aRow["OldValue"] = dRow[column.Ordinal].ToString(); aRow["NewValue"] = iRow[column.Ordinal].ToString(); aRow["ChangeDateTime"] = DateTime.Now.ToString(); aRow["ChangedBy"] = User; AuditTable.Rows.InsertAt(aRow, 0);//Insert the entry } } break; case TriggerAction.Insert: iRow = inserted.Rows[0]; PKString = PKStringBuilder(PKTable, iRow); foreach (DataColumn column in inserted.Columns) { //Build an Audit Entry aRow = AuditTable.NewRow(); aRow["ActionType"] = "I";//I for Insert aRow["TableName"] = TName; aRow["PK"] = PKString; aRow["FieldName"] = column.ColumnName; aRow["OldValue"] = null; aRow["NewValue"] = iRow[column.Ordinal].ToString(); aRow["ChangeDateTime"] = DateTime.Now.ToString(); aRow["ChangedBy"] = User; AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry } break; case TriggerAction.Delete: dRow = deleted.Rows[0]; PKString = PKStringBuilder(PKTable, dRow); foreach (DataColumn column in inserted.Columns) { //Build and Audit Entry aRow = AuditTable.NewRow(); aRow["ActionType"] = "D";//D for Delete aRow["TableName"] = TName; aRow["PK"] = PKString; aRow["FieldName"] = column.ColumnName; aRow["OldValue"] = dRow[column.Ordinal].ToString(); aRow["NewValue"] = null; aRow["ChangeDateTime"] = DateTime.Now.ToString(); aRow["ChangedBy"] = User; AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry } break; default: //Do Nothing break; } AuditAdapter.Update(AuditTable);//Write all Audit Entries back to AuditTable conn.Close(); //Close the Connection } }
//Helper function that takes a Table of the Primary Key Column Names and the modified rows Values //and builds a string of the form "<PKColumn1Name=Value1>,PKColumn2Name=Value2>,......" public static string PKStringBuilder(DataTable primaryKeysTable, DataRow valuesDataRow) { string temp = String.Empty; foreach (DataRow kColumn in primaryKeysTable.Rows)//for all Primary Keys of the Table that is being changed { temp = String.Concat(temp, String.Concat("<", kColumn[0].ToString(), "=", valuesDataRow[kColumn[0].ToString)].ToString(), ">,")); } return temp; } }
The trick was getting the Table Name and the Primary Key Columns. I hope this code is found useful.
I want to be able to create a trigger that updates table 2 when a row is inserted into table 1. However I€™m not sure how to increment the ID in table 2 or to update only the row that has been inserted.
I want to be able to create a trigger so that when a row is inserted into table A by a specific user then the ID will appear in table B. Is it possible to find out the login id of the user inserting a row?
I believe the trigger should look something like this:
create trigger test_trigger on a for insert as insert into b(ID)
When a row gets modified and it invokes a trigger, we would like to beable to update the row that was modified inside the trigger. This is(basically) how we are doing it now:CREATE TRIGGER trTBL ON TBLFOR UPDATE, INSERT, DELETEasupdate TBLset fld = 'value'from inserted, TBLwhere inserted.id= TBL.id....This work fine but it seems like it could be optimized. Clearly we arehaving to scan the entire table again to update the row. But shouldn'tthe trigger already know which row invoked it. Do we have to scan thetable again for this row or is their some syntax that allows us toupdate the row that invoked the trigger. If not, why. It seems likethis would be a fairly common task. Thanks.
Salve, non riesco a disabilitare un trigger su sqlserver nè da queryanalyzer, nè da enterprise manager.In pratica tal cosa riuscivo a farla in Oracle con TOAD, mentre qui nonriesco.Mi interessa disattivarlo senza cancellarlo per poi riattivarlo al bisognosenza rilanciare lo script di creazione.Grazie a tuttiHi I need to disable a DB trigger and I'm not able to do this neither withquery analyzer, neither with enterprise manager.I remeber this job was quite simple using TOAd in Oracle.I'm interested in making it disabled not delete it, without run creationscript.Thanks a lot to everybody.
Hi, I am not sure if this is the right forum to post this question. I run an update statement like "Update mytable set status='S' " on the SQL 2005 management Studio. When I run "select * from mytable" for a few seconds all status = "S". After a few seconds all status turn to "H". This is a behaviour when you have an update trigger for the table. But I don't see any triggers under this table. What else would cause the database automatically change my update? Could there be any other place I should look for an update trigger on this table? Thanks,
Hi all in .net I've created an application that allows creation of triggers, i also want to allow the deletion of triggers. The trigger name is kept in a table, and apon deleting the record i want to use the field name to delete the trigger
I have the following Trigger
the error is at
DROP TRIGGER @DeleteTrigger
I'm guessing it dosen't like the trigger name being a variable instead of a static name how do i get around this?
I have a trigger set on TABLE1 so that any update to this column should set off trigger to write to the AUDIT log table, it works fine otherwise but not the very first time when table1 has null in the column. if i comment out
and i.req_fname <> d.req_fname from the where clause then it works fine the first time too. Seems like null value of the column is messing things up
Any thoughts?
Here is my t-sql
Insert into dbo.AUDIT (audit_req, audit_new_value, audit_field, audit_user)
select i.req_guid, i.req_fname, 'req_fname', IsNull(i.req_last_update_user,@default_user) as username from inserted i, deleted d
Original suggestion for my problem was - Select * from TableA where ID not in ( Select ID from TableB) When I run the query below using the IN operator I get 227 records returned but when I use NOT IN I get zero records when I expect well over 10,000. What am I missing? using SQL 2000 server SELECT LinksInfo.L_ID, LinksInfo.C_ID, Companies.C_CompanyName, Companies.C_Email, Companies.C_CompanyEmailFROM LinksInfo INNER JOIN Companies ON LinksInfo.C_ID = Companies.C_IDWHERE (LinksInfo.L_ID IN (SELECT ZL_ID FROM Location_Zip)) ZL_ID is not a primary key in Location_Zip
Hello again, I think im missing something here, i just cant find out what it is. I have a temp table: CREATE TABLE #tempSearch(tempID BIGINT IDENTITY(1,1) PRIMARY KEY,username NVARCHAR(20) COLLATE Finnish_Swedish_CI_AS,lastlogin DATETIME,signupdate DATETIME) Now i am trying to retrieve some data for each user that is inside this tempSearch list and have an id over xxx (xxx = the value of the parameter @first_id): SELECT @sql = 'SELECT profile_publicinfo.username, profile_publicinfo.gender, profile_publicinfo.signupdate, profile_profilephoto.imageurl, profile_profilephoto.alttext, settings_username.color, profile_publicinfo.lastloginFROM #tempSearch INNER JOIN dbo.profile_publicinfo ON profile_publicinfo.username = #tempSearch.usernameINNER JOIN dbo.settings_privateinfo ON settings_privateinfo.username = profile_publicinfo.usernameFULL OUTER JOIN dbo.profile_coolfacts ON profile_coolfacts.username = profile_publicinfo.username FULL OUTER JOIN dbo.profile_profilephoto ON profile_profilephoto.username = profile_publicinfo.usernameFULL OUTER JOIN dbo.settings_username ON settings_username.username = profile_publicinfo.usernameWHERE (profile_publicinfo.username IN (SELECT username FROM #tempSearch))AND #tempSearch.tempID >= @first_id' SELECT @paramlist = '@first_id int'EXEC sp_executesql @sql, @paramlist, @first_id I need to get the tempID from the tempSearch table in order to compare it with @first_id When i run this i get the same username repeated like 30 times then it moves over to the next, when i debug the #tempSearch it looks fine, just the users that are suppose to be there.
Hi I think I have installed MSDE sucessfully. The new servie is running, but how can I test ifit is working? Can I place my files anywhere on the system? All help appreciated
OK so I'm using dynamic sql to enable me to have a variable for the order by, but now it's giving me the following error
Syntax error converting character string to smalldatetime data type.
Any help is very much appreciated
Code follows
CREATE PROCEDURE spBattingAve1 @cid datetime, @cid1 datetime, @sid nvarchar(10) AS
DECLARE @query nvarchar(4000)
SET @query = 'SELECT MAX(t_batting.runs) AS BestRuns, COUNT(t_batting.dnb) AS DidNotBat, t_player.surname, t_player.firstname, t_batting.player_id, Sum(t_batting.runs) as SumOfruns, COUNT (*) AS Games, COUNT (t_batting.notout) as nout, (Sum(runs)/(COUNT(*)-(COUNT(notout)+COUNT(dnb)))) as AverageRun, (COUNT(*)-COUNT(dnb)) AS Innings, (SELECT COUNT(*) FROM t_batting inner join t_game on t_batting.game_id = t_game.game_id WHERE (t_batting.player_id = t_player.player_id) AND runs >= 50 AND runs <100 and t_game.date >= '+@cid+' And t_game.date <= '+@cid1+' GROUP BY t_batting.player_id) AS Overfifty, (SELECT COUNT(*) FROM t_batting inner join t_game on t_batting.game_id = t_game.game_id WHERE (t_batting.player_id = t_player.player_id) AND runs >= 100 and t_game.date >= '+@cid+' And t_game.date <= '+@cid1+' GROUP BY t_batting.player_id) AS Overton FROM t_batting INNER JOIN t_game ON t_batting.game_id = t_game.game_id INNER JOIN t_player ON t_batting.player_id = t_player.player_id WHERE t_batting.player_id = t_player.player_id AND t_game.date >= '+@cid+' And t_game.date <= '+@cid1+'
GROUP BY t_batting.player_id, t_player.surname, t_player.firstname, t_player.player_id HAVING (COUNT(*)-(COUNT(notout)+COUNT(dnb))) <> 0 order by '+@sid+' DESC'
In SQL 7.0, when i use the below query in query analyzer, I got an error message. why? ======================
Set Identity_insert on
UPDATE Table1 SET no = 3 WHERE no = 4
Set Identity_insert off go
Error message ============= Server: Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'on'. Server: Msg 156, Level 15, State 1, Line 7 Incorrect syntax near the keyword 'off'.
I am on a box with 4 GB of memory that only runs SQL Server. SQL Server is using 1.7 GB as per task manager. There are a lot of databases on this box. I looked at the boot.ini and /3GB was not set. I have done this at least 50 times before: turn on the /3GB switch and SQL Server will use up to 2.7 GB per task manager. (I know it is actually 2GB and 3GB but it seems that in task manager it shows up as 1.7-1.8 and 2.7-2.8)
Anyway back to the story. So I set /3GB, rebooted the server and SQL Server still only consumes up to 1.7GB. I realize that SQL Server might not actually need more memory, but I have a distinct feeling that it wants more but is being constrained; that is, the the /3GB switch is not working. More relevant information:
1) the boot.ini line: multi(0)disk(0)rdisk(0)partition(1)WINNT="Microsoft Windows 2000 Advanced Server" /fastdetect /3GB
2) max server memory (MB) = 3000
3) min server memory (MB) = 3000
4) awe enabled = 0
5) @@version = 8.00.2039 which is SQL Server 2000 SP4
Has anyone seen this before? Any ideas on how to troubleshoot this?