--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, 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
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
-------------------------------- 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.
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?
What would be the best practice to prevent users who didn't create a record in sql from deleting? When a record is created I have the username who created the record in one of the fields. I was thinking maybe a query?
It is an option to set deletion without getting logged since I have problem to delete two years historical data and would like to keep this year data on my 80MB rows. Actually I create a new table to get copy one-year data and I truncated the old table. I am wondering if there is other better way to do this task.
We are currently setting up out production server to the following requirements:
1. Every month, delete records that haven't been changed in the last 90 days. 2. Replicate insert statements to a backup database which will keep track of all data, and act as an archive/data warehouse.
The first step is easy, as it is just a script that checks the date of the last change on each row. However, the second step is a bit more tricky. We tried setting up replication between two test databases, but we ran into the following problem: Whenever old data has been deleted in the production database, the replication agent deletes it in the data warehouse database too.
Is it possible to override or disable this, so data is only inserted/updated, and not deleted? No applications using the database deletes records, so database integrity should not be a problem.
While performing import actions I had a system freeze, when the system returned the sessions had been closed and the database had vanished, with the help of support we recovered the database only to find that the original project ID had a suffix attached ( Original 40/0110, New 40/0110-1 ), when I try to return it to it's original numbering convention it says it has to be a unique number which suggests to me it is not deleted but hiding in the background, can the original be recovered or is it possible to renumber the recovered database, I have searched the whole of the databases and the original is nowhere to be seen.
I want to try and protect myself from my own stupidity. I have a number of sql databases, but one is LIVE. It is easy to drop tables but I want to set something (e.g. a password) which will help prevent me from dropping tables on the live database.
Hi Everyone,I have a table in which their is record which is exactly same.I want to delete all the duplicate keeping ony 1 record in a table.ExampleTable AEmpid currentmonth PreviousmonthSupplimentarydays basic158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.004701.00158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.004701.00158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.004701.00I want to delete 2 rows of above table.How can I achieve that.Any suggestion how can i do that.Thank you in advanceRichard
Ok, so I have an issue, was wondering if anybody else has any suggestions.
I have a table that is pretty large, in all regards. It is a "message" table that holds text messages that users send to each other.
1. Has some data fields, integers, dates, some bit columns, a message subject field (varchar(250)), and a message body field (field type = text) 2. Table contains about 70 million records 3. Table has 6 indexes associated to it 4. Table has 2 views associated to it. 5. Table has 8 foreign keys associated to it.
I need to delete, oh, about 90,000 records out of this 70 million record table. I am able to disable the foreign keys to this table for deletion, but that does not seem to mitigate the problem. I think the issue lies with having to update the indexes as well as the views.
When I execute the select statement to retrieve the records I need to delete, it executes pretty quickly, no problems there that I can see.
The issue comes when I try to delete the records, it takes way too long, and we know it. We let it run for an hour and it didn't really get anywhere. This is in a server environment, some pretty decent hardware, 8gig memory, fast SCSI drives, 8 core processors, i don't know the exact specifics, but they're not bad.
This is from our dev environment which is but a portion of our production db- but I presume our production environment will have similar percentages (not necessarily the pages scanned)
Any suggestions on how to delete records efficiently?
Im using SQL enterprise manager v8, a few days ago I got a report that a user account was deleted. I was wondering what logs would point this out. I've been through the event review and i am not seeing any usefull info.
Obviously to delete all records from DB table is simple, however, I would like to make my whole Live DB pretty much empty. I've copied all my data from my test DB over to my live DB (didn't mean to but I did). I would like to remove all the data and the identity values, resetting them back at their original values. Is there a simple way or do I have to do it the hard way. That being going in and removing Identity, saving and then placing identity back on the DB Table.
i am trying to delete rows where a particular column (hours) has the same value for the same member (primary key) but where the effective dates are different. i want to delete the duplicate(s) rows which have the most recent effective date(s).
Basically, I create a database with sql, then I delete it manually(not via sql statment. This is a problem which I realise. In fact, you can't delete the database because the VS 2005 still is using it) I run the same code again, then it says the database still exists, even it is physically destroied.
------Here is the errors: System.Data.SqlClient.SqlException: Database 'riskDatabase' already exists. at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolea n breakConnection)
------The evidence that the database doesn't exist physically: Unhandled Exception: System.Data.SqlClient.SqlException: Cannot open database "riskDatabase" requested by the login. The login failed.
------The code: /* * C# code to programmically create * database and table. It also inserts * data into the table. */
using System; using System.Collections.Generic; using System.Text;
using System.Configuration; using System.Data; using System.Data.SqlClient; using System.IO;
namespace riskWizard { public class RiskWizard { // Sql private string connectionString; private SqlConnection connection; private SqlCommand command;
Ladys, Gentlement, I have table that grows anywhere from 200,000 to 1,000,000 records perday. Besides that I need to keep at least 6 months historical data from this same table. The transaction log was purged after each batch when testing data monthly. I'm looking for some way of deleting just one day's data if it meets a criteria. It must remain within the 6 months period of historical data. This is what I've come up with so far"
select * FROM dbo.Temp_table WHERE datediff(day, DATE_TIME, getdate()) >= 180
If it meets this criteria I can change the select to a delete? Please Let me know what you think
i am using this statement for deleting a single row in sql table. "DELETE FROM Random WHERE NewID= '" & strwinner & "'"
where "strwinner" is the variable which contains the row to be deleted. the problem is that when i check the table in sql the row which was supposed to be deleted is sitll there.it does not give me any error statement or something. iam executing this statement by using ExecuteNonQuery in my .aspx page. please help
We have an employee table that contains bank details and are experiencingproblems with account numbers being erased and lost. In order to track downwhy this is happening (either due to our application code or SQLreplication) we'd like to be able to prevent certain columns from beingdeleted if they already contain some data.Is it possible to setup a check constraint to prevent our ee_acct_no columnsfrom being set to NULL or blank strings if it contains an account number(i.e a 9 digit number)? We have setup the column to allow NULL's as we don'talways know employees bank details until later, so we do need to put them onour database without bank details initially.Also, if possible, can someone suggest a stored procedure or trigger i couldcreate that would fire a user-defined error message that would email anoperator if a bank account number changed?Many thanksDan Williams.
I was trying to relocate my transaction log to a bigger drive usingsp_movedevice but I made a mistake in the syntax of the second parameterand put only the path, not the path and the file name.Now my database is marked as "suspect" and I get an error message in my logupon database start up saying that the log file cannot be open.Is there a way to have MS SQL 6.5 "forget" all the logs of this database,create new ones and restart the database? The logs contained nothingimportant, I had truncated them an hour or so before I made my mistake. Ijust want to make sure the data are still usable.When I look at the devices with sp_helpdevice, I can see a log that existand is hopefully in pristine condition and the one that doesn't existanymore.I looked in the archives of various newsgroups but couldn't find somethingthat correspond closely to my situation. I saw something similar but withMS SQL 7.0(http://groups.google.com/groups?hl=...om %26rnum%3D4)using sp_attach_db/sp_detach_db. What would be the equivalent with version6.5?Thanks!Charles--Charles-E. Nadeau Ph.Dhttp://radio.weblogs.com/0111823/
