Problem At Writting Triggers
Oct 6, 2006
hi,
here i had created two table named table1,table. both the tables are homogenously belongs to same group. if i eneter any data say pinno in table1 the same pinno has to be enterd into table so i had wirtten a trigger for table here like this:
create trigger Tg_Table1 on Table1
for insert as
insert into Table2(pinno)
select pinno as pinno from table1.
the query is getting compiling correctly but the data entered into table2 while entering into the table1 is coming with duplicate values like:
output of table2:
pinno
1001 -> when i inserted 1001 in table1
1001 -> when i inserted 1002 in table1
1002
1001 -> when i inserted 1003 in table1
1002
1003
the column name pinno in table1 is also same for table2.
intially i had primary keys for two tables for column pinno. at that time it has shown error wqhile i am trying to insert data into table1.
so i dropped the PK constarint for table2. this time the out put coming with replicated values.
pls some one show me a way to clear this problem
One can never consent to creep,when one feels an impulse to soar
RAMMOHAN
View 1 Replies
ADVERTISEMENT
Feb 13, 2001
Hello,
I have to write a trigger to truncate at a scheduled time.Truncate statement is not allowed in the trigger.What should I do.I mean how to go for it.
Thanks for any help.
JAY
View 3 Replies
View Related
Feb 13, 2001
Hello,
I have to write a trigger to truncate a table at a scheduled time.Truncate statement is not allowed in the trigger.What should I do.I mean how to go for it.How can I test my trigger if it's working fine or not.
Thanks for any help.
JAY
View 2 Replies
View Related
Jun 25, 2007
I have table name employee with attributes like eno,ename dno,salary. My problem is that i need select complete information of the emloyee's when employee getting same salary in same depatment.
Eg: I have the table like this
eno ename dno salary
1 Noor D10 10000.50
2 Ahmed D20 20000.75
3 jaya D30 15000.65
4 hema D10 10000.50
5 rupa D20 20000.75
6 prabha D40 20000.75
7 lalitha D40 12000.75
8 vetri D40 12000.75
and i need the output as fallows
eno ename dno salary
1 Noor D10 10000.50
4 hema D10 10000.50
7 lalitha D40 12000.75
8 vetri D40 12000.75
2 Ahmed D20 20000.75
5 rupa D20 20000.75
so I written the query like this
SELECT * FROM employee
where dno in(SELECT dno FROM employee
group by dno,salary
having count(dno)>1) and
salary in(SELECT salary FROM sub
group by dno,salary
having count(salary)>1)
but i got the output as fallows
eno ename dno salary
1 Noor D10 10000.50
4 hema D10 10000.50
7 lalitha D40 12000.75
8 vetri D40 12000.75
2 Ahmed D20 20000.75
5 rupa D20 20000.75
6 prabha D40 20000.75
so plz somebody help me to get the output what i need........
Noor Ahmed
Keysoft solution
View 6 Replies
View Related
Jul 2, 2007
Hallo
We got a €œSql Server€? and an €œAS400€? System. I can Read the information from the AS400 using a DataReader- Source my problem ist that I can not manage to write in the €œAS400€?. Using a DataReader- Destination I am not able to specify the destination table.
Does any one know what I am missing to Configure?
View 4 Replies
View Related
May 1, 2008
hello.
i have two table so that they are following form:
tblCardex(Fd_GoodsId,Fd_RepositoryId,Fd_DateTime,Fd_Type,Fd_UnitCoefficent)
tblGoodsStock(Fd_GoodsId,Fd_RepositoryId,Fd_UnitCoefficent)
Fd_GoodsId,Fd_RepositoryId -->inteager
Fd_UnitCoefficent --> decimal
Fd_Type --> bit (0 means input and 1 means output)
i wish writting a trigger on Insert of tblCardex.in this trigger ,i must do following operations:
if(tblGoodsStock.Fd_GoodsId=Inserted.Fd_GoodsId and tblGoodsStock.Fd_RepositoryId=Inserted.Fd_RepositoryId)
begin
if(Inserted.Fd_Type==0)
tblGoodsStock.Fd_UnitCoefficent = tblGoodsStock.Fd_UnitCoefficent+
Inserted.Fd_UnitCoefficent
else --Inserted.Fd_Type==1
tblGoodsStock.Fd_UnitCoefficent = tblGoodsStock.Fd_UnitCoefficent-
Inserted.Fd_UnitCoefficent
end
tank you.
View 5 Replies
View Related
Feb 14, 2006
Hi All,
I need to read data from a table, on a remote server, and write the data to an identical table on the local server. (That I did and all works fine.) The local server is were we do analysis services. So, with the data from the remote, I need to write parts of it to 2 tables other than the local identical table. (I hope this is clear) What tools do I use to accomplish this?
If this is not clear, let me know and I will try to clarify.
Your help is very much appreciated.
Thanks,
Roy
View 1 Replies
View Related
Feb 28, 2008
Can someone please help I'm writting the following query.
SELECT
(SELECT c.column_name FROM information_schema.tables T
JOIN information_schema.columns C
ON t.table_name = c.table_name
WHERE t.table_type = 'base table' and t.table_name like 'L_%' )
INTO #TempTable FROM TableA A LEFT JOIN [Server-Name].DB_Name.dbo.TableB B ON A.ID = B.ID
I'm trying to put commas between column names. How do I go about doing that?
View 16 Replies
View Related
Apr 7, 2006
Hi:
I'm trying to write a transformation script to write multiple rows from 1 input row. In SQL Server 2000 DTS I could do this by using DTSTransformStat_SkipFetch and DTSTransformStat_SkipRow. In SQL server 2005 it looks like I need to use EndOfRowset but I do not know how to use it for sure.
If this correct can someone show me a code example and if I am incorrect can someone tell me how to do this?
Thanks
View 19 Replies
View Related
Aug 24, 2006
Hi Friends,
I am trying to write SQL statement for getting information about Cube metadata.
When we create any table in MSSQL server, we can trace out its information from system tables in master database by querying tables like sysobjects, syscolumns etc.
Like that I have created a cube using MS Analysis services and I can see Metadata (like Creation Date, Processed on etc) on 'Metadata' tab of cube. But I want to access its contents by writting SQL statement. For this I could not locate any system level table where this information gets stored.
Could any one help me for writting htis system table level query for accessing metadata info of cube?
Thanks in advance.
Avadhoot
View 3 Replies
View Related
Jul 20, 2005
HiI am trying to write a report that calculates the average number of salesover 7, 14, 31 and 365 days for each hourly period of the day. the problemis it takes over 4 minutes to run.e.g.Average Xactions per Hour7 Days 14 Days 31 Days 365 Days00:00 - 01:00 1,141.6 579.2 261.6 28.801:00 - 02:00 1,298.0 649.6 293.4 30.0The report was use to be purely ASP running SQL Statements.I then changed it to ASP Running a SP 24 times - this reduced running timeby about 1 minute.I then changed it so that the stored proc looped internally 24 times andreturns the data.I have ran the Index Tuning Wizard on the SQL and Implemented the indexessuggested - this actually increase execution time by 20 seconds.Below is the stored procedure I am currently using that loops internally 24times and returns the data.Can anyone suggest a better way / any improvements I could make ?Many ThanksSteve-----------------------------------------------------------------------------------------------------CREATE procedure ams_RPT_Gen_Stats@strResult varchar(8) = 'Failure' output,@strErrorDesc varchar(512) = 'SP Not Executed' output,@strTest varchar(1),@strCurrency varchar(3),@strVFEID varchar(16)asdeclare @strStep varchar(32)set @strStep = 'Start of Stored Proc'/* start insert sp code here */create table ##Averages (TheHour varchar(2),Day7Avge float ,Day14Avge float ,Day31Avge float ,Day365Avge float)declare @numHour varchar(2)declare @strSQL varchar(2000)declare @Wholesalers varchar(64)declare MyHours cursor FORWARD_ONLY READ_ONLY forselect convert(char(2), timestamp,14) as TheHourfrom xactionsgroup by convert(char(2), timestamp,14)order by convert(char(2), timestamp,14)if @strTest = 'Y'select @Wholesalers = VALUE FROM BUSINESSRULES WHERE NAME ='TEST_Wholesalers'open MyHoursfetch next from MyHours into @numHourwhile @@fetch_status = 0beginset @strSQL = 'insert into ##Averages (TheHour, Day7Avge) ( select ''' +@numHour + ''', ' +'count(*) / 7.00 ' +'FROM ' +'XACTIONS INNER JOIN ' +'RETAILER ON XACTIONS.RETAILERID = RETAILER.RETAILERID ' +'WHERE ' +'(DATEDIFF(DAY , xactions.timestamp , GETDATE() ) < 8) and ' +'xactions.xactiontotal <> 0 and ' +' convert(char(2) , timestamp, 14) = ''' + @numHour + ''' 'if @strTest = 'Y'set @strSQL = @strSQL + ' and retailer.BillOrgID not in (' +@Wholesalers + ') 'if @strCurrency <> '*'set @strSQL = @strSQL + ' and xactions.XACTIONCURRENCY = ''' +@strCurrency + ''' 'if @strVFEID <> '*'set @strSQL = @strSQL + ' and xactions.VFEID = ''' + @strVFEID + ''''set @strSQL = @strSQL + ')'exec ( @strSQL )set @strSQL = 'update ##Averages set Day14Avge = ( select ' +'count(*) / 14.00 ' +'FROM ' +'XACTIONS INNER JOIN ' +'RETAILER ON XACTIONS.RETAILERID = RETAILER.RETAILERID ' +'WHERE ' +'(DATEDIFF(DAY , xactions.timestamp , GETDATE() ) < 15) and ' +'xactions.xactiontotal <> 0 and ' +' convert(char(2) , timestamp, 14) = ''' + @numHour + ''' 'if @strTest = 'Y'set @strSQL = @strSQL + ' and retailer.BillOrgID not in (' +@Wholesalers + ') 'if @strCurrency <> '*'set @strSQL = @strSQL + ' and xactions.XACTIONCURRENCY = ''' +@strCurrency + ''' 'if @strVFEID <> '*'set @strSQL = @strSQL + ' and xactions.VFEID = ''' + @strVFEID + ''' 'set @strSQL = @strSQL + ') where TheHour = ''' + @numHour + ''' 'exec ( @strSQL )set @strSQL = 'update ##Averages set Day31Avge = ( select ' +'count(*) / 31.00 ' +'FROM ' +'XACTIONS INNER JOIN ' +'RETAILER ON XACTIONS.RETAILERID = RETAILER.RETAILERID ' +'WHERE ' +'(DATEDIFF(DAY , xactions.timestamp , GETDATE() ) < 32) and ' +'xactions.xactiontotal <> 0 and ' +' convert(char(2) , timestamp, 14) = ''' + @numHour + ''' 'if @strTest = 'Y'set @strSQL = @strSQL + ' and retailer.BillOrgID not in (' +@Wholesalers + ') 'if @strCurrency <> '*'set @strSQL = @strSQL + ' and xactions.XACTIONCURRENCY = ''' +@strCurrency + ''' 'if @strVFEID <> '*'set @strSQL = @strSQL + ' and xactions.VFEID = ''' + @strVFEID + ''' 'set @strSQL = @strSQL + ' ) where TheHour = ''' + @numHour + ''' 'exec ( @strSQL )set @strSQL = 'update ##Averages set Day365Avge = ( select ' +'count(*) / 365.00 ' +'FROM ' +'XACTIONS INNER JOIN ' +'RETAILER ON XACTIONS.RETAILERID = RETAILER.RETAILERID ' +'WHERE ' +'(DATEDIFF(DAY , xactions.timestamp , GETDATE() ) < 366) and ' +'xactions.xactiontotal <> 0 and ' +' convert(char(2) , timestamp, 14) = ''' + @numHour + ''' 'if @strTest = 'Y'set @strSQL = @strSQL + ' and retailer.BillOrgID not in (' +@Wholesalers + ') 'if @strCurrency <> '*'set @strSQL = @strSQL + ' and xactions.XACTIONCURRENCY = ''' +@strCurrency + ''' 'if @strVFEID <> '*'set @strSQL = @strSQL + ' and xactions.VFEID = ''' + @strVFEID + ''' 'set @strSQL = @strSQL + ' ) where TheHour = ''' + @numHour + ''' 'exec ( @strSQL )fetch next from MyHours into @numHourend -- while fetchclose MyHoursdeallocate MyHoursselect * from ##Averages order by TheHourdrop table ##Averages/* end insert sp code here */if (@@error <> 0)beginset @strResult = 'Failure'set @strErrorDesc = 'Fail @ Step :' + @strStep + ' Error : ' +CONVERT(VARCHAR,@@Error)return -1969endelsebeginset @strResult = 'Success'set @strErrorDesc = ''endreturn 0GO
View 1 Replies
View Related
Sep 3, 2007
I am using Sql Server 2000.
I have a customer table with fields - CustId, Name, Address, City, StdCode, Phone.
I used to insert entries in this table from an excel file.
One excel file will contain thousands of customer.
In this table combination of StdCode and Phone should not be repeated.
If I do it in my VB.Net coding.then application gets drastically slow.
So I want to write a procedure or trigger for this.
Here what I will do, I will send all records into database then this trigger or procedure will check for
any existing entry of combination of StdCode and phone. If entry exists then this will delete new entry
or will not allow this new entry.
Is this possible to do using Trigger or stored procedure?
View 5 Replies
View Related
May 1, 2007
In both VB6 and VB2005 Both are loaded on same computer. When attempting to write to an access database table using ADO I get a runtime error: -2147217887(80040e21) "mutiple-step operation generates errors. Check each status value. " Reading the database is no problem. I only get the errors when writting to database. I have used the following code forever in VB6 and never got this error. Now that I have Visual Studio 2005 loaded on the same machine. I get this error. I get the exact same error when using this code in VB2005. I know VB6 but am just learning 2005. The following code is writting to a database table from various user imputed text and combo boxes. I used basicly the same code in VB2005 and get the same exception error. This code works on machines that do not have VB .net loaded. How do I fix this. My application is useless unless the user can make changes to their data.
Public Function ActionToDb()
Dim noPo As Integer
Dim SQL As String, rst As ADODB.Recordset, dataSourceName As String
dataSourceName = DBPATH & "LVBURV Database.mdb"
dbConn.ConnectionString = "DBQ=" & dataSourceName & ";Driver={Microsoft Access Driver (*.mdb)}"
dbConn.Open
On Error GoTo ErrorHandler
Set rst = New ADODB.Recordset
With rst
SQL = "Select [mLVbuNo], [mStratInit], [mMerticLv],[mSubMetLv],[mTaskAction],[mImpact]," & _
"[mStatus], [mOwner], [mTitle],[mOrigDate],[mCurrentDate],[mCopmpleteDt],[mComment],[mSource]," & _ "[mStatColor],[mHyperlink],[CompCheck] From [tblMasterActionItem]"
.CursorLocation = adUseClient
.Open SQL, dbConn, adOpenKeyset, adLockBatchOptimistic, adCmdText
noPo = .RecordCount
'If .RecordCount > 0 Then
.MoveLast
.AddNew
rst.Fields("mLVbuNo") = giRecNo
rst.Fields("mStratInit") = frmActionDe.cboStatInit.Text
rst.Fields("mMerticLv") = frmActionDe.cboMetricLever.Text
rst.Fields("mSubMetLv") = frmActionDe.cboSubMet.Text
rst.Fields("mTaskAction") = frmActionDe.txtTask.Text
rst.Fields("mImpact") = frmActionDe.txtImpact.Text
rst.Fields("mStatus") = frmActionDe.txtStatus.Text
rst.Fields("mOwner") = frmActionDe.cboOwn.Text
rst.Fields("mTitle") = gstOwnT
rst.Fields("mOrigDate") = frmActionDe.txtOrigDt.Text
rst.Fields("mCurrentDate") = frmActionDe.txtCurDt.Text
rst.Fields("mCopmpleteDt") = frmActionDe.txtCompDt.Text
rst.Fields("mComment") = frmActionDe.txtComments.Text
rst.Fields("mSource") = frmActionDe.cboSource.Text
rst.Fields("mStatColor") = frmActionDe.txtStatColor.Text
rst.Fields("mHyperlink") = frmActionDe.txtHyperlink.Text
If optCompCheck.Value = True Then
rst.Fields("CompCheck") = True
Else
rst.Fields("CompCheck") = False
End If
'End If
.Update
.Close
End With
dbConn.Close
ExitSub:
On Error Resume Next
dbConn.Close
Set rst = Nothing
Set dbConn = Nothing
Exit Sub
ErrorHandler:
WriteToErrorLogEnd Err.Description & " in ActionToDb"
Resume
End Function
View 6 Replies
View Related
May 12, 2008
This 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
View 12 Replies
View Related
Jan 3, 2007
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 Related
May 31, 2007
Hello 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
View 1 Replies
View Related
Jun 15, 2007
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.
View 2 Replies
View Related
Dec 6, 2007
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
View 3 Replies
View Related
Dec 21, 2007
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 Related
May 9, 2004
Hi 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
View 3 Replies
View Related
Jun 17, 2004
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?
View 10 Replies
View Related
Mar 5, 2006
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 Related
Dec 4, 2001
l'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
View 4 Replies
View Related
Dec 5, 2001
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'.
View 1 Replies
View Related
Jan 11, 2002
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 ??
View 1 Replies
View Related
Jan 31, 2002
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 :)
View 1 Replies
View Related
Aug 3, 2001
Hey everyone...
I'm working on trying to figure out how to update the child table from the parent via a trigger. This works fine as long as the value is in the child table. If it's not, then I get my foreign key violation. So, My next thought was to simply put in a begin tran / rollback tran within the trigger... My question is this : If I have multiple triggers on the parent table all based on the update of the key field (and the child tables... some 7 of them) all have FK's, if I rollback one trigger does it rollback the entire transaction? Or just the functionality of that trigger? From what I've read, it appears as if it's the entire transaction... if so, how do I get a trigger to 'ignore' itself or not fire based on a select criteria that i have within that same trigger...
Thank you...
View 3 Replies
View Related
Oct 24, 2001
I just tried creating my first trigger and I'm a little bit confused. I am trying to see if certain fields have changed and will do that by checking the values of the fields in the "master" table and the "Inserted" table. I wasn't sure which table would have the old data and which would have the new, so I created the following trigger to help me determine which was which:
CREATE Trigger upd_cost_master On tbl_cost_master
For Insert, Update As
Declare @U_Name varchar(65)
Declare @O_Name varchar(65)
Select @U_Name=I.submitted_by, @O_Name=O.submitted_by
From tbl_cost_master O Inner Join Inserted I On O.autokey = I.autokey
Print 'Inserted: ' + @U_Name
Print 'tbl_cost_master: ' + @O_Name
Commit Tran
To my surprise, @U_Name and @O_Name contained the same values. I though that one table contained the old values and one contained the new. Was I wrong? If so, is there anyway to compare the Old and New values through a trigger?
Chris
View 1 Replies
View Related
Jul 20, 2000
Hi,
I'm using SQL Server 7.0. I have an insert trigger on table t05r. I populate table t05r with a DTS package. The trigger is supposed to add the record which was inserted into t05r, into t39r. After I run the DTS package to populate t05r, I checked t39r and no records where added. What is going wrong? Is it that DTS uses bulk copy and triggers don't fire on bulk copy?
Any help would be greatly appreciated.
Thanks in advance,
Darrin
View 1 Replies
View Related
Nov 1, 1999
I have an insert trigger on table A which inserts records into table B.
This insertion into B fails( or may fail ) at certain conditions.
I do not want the insertion into A to be affected because of this. Any ways to achieve this ?
View 1 Replies
View Related
Oct 8, 1999
Hi,
I have 3 tables A,B C and Triggers on each table like.
for insert/update on table 'C' calculates sum(xxfield) and updates
table 'B'.
for insert/update on table 'B' calculates sum(yyfield) and updates
table 'A'.
This works fine if I insert rows OneByOne.
Problem :- Doesn't work if I post entries(hundreds) in to table 'C' using a Query.
whats happening. Can anyone help Me????????
View 1 Replies
View Related
Nov 12, 2001
How do I know if the triggers on table has been disabled or not. Thanks for your ideas!!!!
View 3 Replies
View Related
Nov 21, 2002
Hello,
I've update triggers on my table, But for some of the transactions, I don't want the triggers to fire. I know in Sybase there are set triggers off. In MS SQL, Is there any similar commands which will set triggers off for particular sql statements.
Thanks A Lot
Sejal
View 2 Replies
View Related
