Proc With Dynamic Query Hang When Insert Trigger Fires....
Feb 16, 2005
Need to parsing serverName and databaseName to run a dynamic query to get serverName and databaseName and employee_ID via a accountID parameter.
-----------------------------
declare @stringSQL varchar(200)
select @stringSQL=
'insert into temp1 select '+@AccountID+' accountID, employee_ID from ' + @serverName +'.dbo.'+@databaseName+'.tblEmployee where inactive=0'
print @stringSQL_GetUserName
exec (@stringSQL_GetUserName)
select * from temp1
------------------------------
above dynamic query works fine.
Howevery, this should be run only under insertion event. When I put it in a proc to run within the insertion trigger or put the whole sql statement within the trigger:
1. when ran at a MSDE server
MSDTC on server is unavailable.
2. when ran at a SQL2000 developer testing server with the distributed transaction coordinator on, the insertion a record in the isql/w hang there. Could not even to kill this query, and have to stop and restart the SQL server.
Then I just want to return the dynamic query result without 'insert into temp1 ', the result is still hang...
Is there a way to let the insert trigger to run a dyanamic query which linked to around 10 servers?
thanks
David
View 4 Replies
ADVERTISEMENT
Jan 19, 2000
If I update a record such as
Update MyTable SET MyField = 'MyValue'
where MyKey = 'Key1'
The trigger fires .....
If I update multiple records
Update MyTable SET MyField = 'MyValue'
where MyKey IN ('Key1','Key2')
Does the trigger (Update) on MyTable fire more than once or once.
View 4 Replies
View Related
Apr 12, 2007
hi,
I want a trigger in db aaa to fire when table a_aaa is updated in server a and table b_bbb in db bbb in server b to be populated with data. I know how to write a trigger if fired and the result stays in one server with one db. But I don't know how to do it if between two servers and two db.
a
win server 2003 standard Edition
sql Server 2000
db:aaa
table: a_aaa
column:a_aaa_a
b
win server 2003 standard Edition
sql Server 2000
db:bbb
table:b_bbb
column: b_bbb_b
It's not working because the symtax is incorrect and because I am not sure how to do it between two servers. If it is not correct, where am I wrong? Where should each line be located?? et cetera......
Can anyone help?
Thanks in advance.
CREATE TRIGGER [enddate_changed_on_alert] ON [dbo].[USER_DATA]
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @EndDate as DATE
DECLARE @CompCode as VARCHAR(15)
DECLARE @PhoneGMSM as VARCHAR(10)
DECLARE @PhoneALERT as VARCHAR(10)
SELECT @PhoneALERT=phone1 from AUSSMEDEVIQ1.QDB_Test.USER_DATA;
SELECT @PhoneGMSM=PHONE1 from AUSADFORMULA02.QDB_KS.dbo.USER_DATA_DEVIQ;
UPDATE AUSADFORMULA02.QDB_KS.dbo.USER_DATA_DEVIQ SET EXT4 = "111"
WHERE AUSSMEDEVIQ1.QDB_Test.USER_DATA.@Phone = AUSADFORMULA02.QDB_KS.dbo.USER_DATA_DEVIQ;
WHERE:
USER_DATA_DEVIQ = b_bbb_b
AUSADFORMULA02 = bbb
QDB_KS = b_bbb
USER_DATA = a_aaa_a
AUSSMEDEVIQ1 = aaa
QDB_Test = a_aaa
END
GO
View 9 Replies
View Related
Nov 21, 2007
I have an update trigger that works wonderful as long as I am updating the row in Enterprise Manager, but if I update the same column and row using an update statement in Query Analyzer the trigger doesn't fire.
Any ideas why?
TIA.
View 13 Replies
View Related
Aug 14, 2006
Dear Experts,I'm an Oracle guy, who is being given more SQL Server assignmentslately.I've been looking for things on the web about this, but I can'tanything so far.In Oracle, I you can create a trigger on a table that -only- fires ifcertain fields are updated.create or replace trigger trg_some_triggerBEFORE insertOF some_field1, some_field2on tbl_some_tablefor each row....Is this also possible in SQL Server?What is the syntax please?Thanks a lot!
View 2 Replies
View Related
May 3, 2006
Hi
In SQL Server 2005, does TRIGGER for DML fire per statement or per Record ?
If both, can we define the way it should fires.
thanks / ramanuj
View 11 Replies
View Related
Mar 24, 2007
I have a Stored Procedure for processing a Bill of Material.
One column on the Assembly Table is a Function Name that contains some busniess rules.
OK, now I'm doing a Proof of Concept and I'm stumped.
Huuuuh!
I will ultimately have about 100 of these things. My plan was using Dynamic SQL to go execute the function.
Note: The function just returns a bit.
So; here's what I had in mind ...
if isnull(@FnNameYN,'') <> ''
exec spinb_CheckYN @FnNameYN, @InvLineID, @FnBit = @FnBit output
CREATE PROCEDURE dbo.spinb_CheckYN
@FnNameYN varchar(50),
@InvLineID int,
@FnBit bit output
AS
declare @SQL varchar(8000)
set @SQL = '
if dbo.' + @FnNameYN + ' (' + convert(varchar(31),@InvLineID) + ')) = 1
set @FnBit = 1
else
set @FnBit = 0'
exec (@SQL)
GO
Obviously; @FnBit is not defined in @SQL so that execution will not work.
Server: Msg 137, Level 15, State 1, Line 4
Must declare the variable '@FnBit'.
Server: Msg 137, Level 15, State 1, Line 5
Must declare the variable '@FnBit'.
So; is there a way to get a value out of a Dynamic SQL piece of code and get that value INTO my OUTPUT variable?
My many thanks to anyone who can solve this riddle for me.
Thank You!
Sigh: For now, it looks like I'll have a huge string of "IF" statements for each business rule function, as follows:
Hopefully a better solution comes to light.
------ Vertical Build1 - Std Vanes -----------
if @FnNameYN = 'fnb_YN_B1_14'
BEGIN
if dbo.fnb_YN_B1_14 (convert(varchar(31),@InvLineID) ) = 1
set @FnBit = 1
else
set @FnBit = 0
END
------ Vertical Build1 - Scissor Vanes -----------
if @FnNameYN = 'fnb_YN_B1_15'
BEGIN
if dbo.fnb_YN_B1_15 (convert(varchar(31),@InvLineID) ) = 1
set @FnBit = 1
else
set @FnBit = 0
END
.
.
.
etc.
View 10 Replies
View Related
Jul 12, 2005
I'm trying to update (increment)
Company.SumtotalLogons
from CompanyUsers.NumberOfLogons
where CompanyUsers.CompanyID = Company.CompanyID
I'd like to either write a formula (if it is even possible to fire a formula from one table update/insert to increment a field in another table), or a stored procedure that triggers an auto update/append into Company.SumTotalLogons
I know this is possible in access, so i'm wondering how to go about it in ms-sql?
any ideas?
View 1 Replies
View Related
Feb 23, 2007
I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
View 1 Replies
View Related
Nov 22, 2004
Please help, I am trying to write a dynamic search stored procedure using three fields.
I use the same logic on the front end using VB to SQL server which works fine, but never tried a stored procedure with the logic.
Can you please help me, construct a stored procedure.
User can choose any of the three(progno, projno, contractno) fields as a where condition.
I ma using asp.net as front end with sql server backend.
CREATE PROCEDURE dbo.USP_Searchrecords
(@ProgNO nvarchar(50),
@ProjNOnvarchar(50) ,
@ContractNOnvarchar(50))
AS
DECLARE @myselect nvarchar(2000)
DECLARE @psql nvarchar(2000)
DECLARE @strsql nvarchar(2000)
SET NOCOUNT ON
@psql = "SELECT * FROM Mytable"
IF @ProgNO <> '' then
strsql = WHERE ProgNO = @ProgNO
end if
If @ProjNO <> '' then
if strsql <> '' then
strsql = strsql & " and ProjNO =@ProjNO
ELSE
strsql = wHERE ProjNO =@ProjNO
END IF
END IF
If @ContractNO <> '' then
if strsql <> '' then
strsql = strsql & " and ContractNO =@ContractNO
ELSE
strsql = wHERE ContractNO =@ContractNO
END IF
END IF
@myselect = @psql + @strsql
EXEC(@myselect)
Please help. Thank you very much.
View 3 Replies
View Related
Oct 12, 2004
How can I make this stored procedure concept work:
-----------------------------------------------
Create Procedure MyProc @MyValue varchar(5)
As
Declare @ColumnName as varchar(11)
Set @ColumnName = 'Price_' + @MyValue
Select Sum(Price) As @ColumnName --????
From MyTable where Item = @MyValue
Return
-----------------------------------------------
Using @MyValue = 23 should make the Sum(Price) column name 'Price_23'. It's not working. What am I doing wrong?
Bjorn
View 2 Replies
View Related
Sep 28, 2007
I have a table with 52 columns named 'Week1', 'Week2' etc. with values 1, 0 etc. I want to check values in each column. I have following lines in my procedure.
Declare @l_str varchar(50),
@l_count int
Select @l_count = 1
Select @l_str = 'Week' + Convert(varchar, @l_count)
Now how do I compare the value stored in the @l_str which should be wither 0 or 1 and not 'Week1'?
Is there any better method to compare read these 52 table variables?
Thanks in advance
View 3 Replies
View Related
Jul 20, 2005
I am trying to run 3 dynamic selects from stored proc, really onlythe table name is dynamic.. Anway I'm kinda lost on how I canaccomplish this.. this is what I have but it only returns the firstresult.. that being basicCREATE PROCEDURE email_complexity@TableName VarChar(100)ASDeclare @SQL VarChar(1000)Declare @SQL1 VarChar(1000)Set nocount onSELECT @SQL = 'SELECT Count(complexity) AS basic FROM 'SELECT @SQL = @SQL + @TableNameSELECT @SQL = @SQL + ' WHERE len(complexity) = 5'Exec ( @SQL)SELECT @SQL1 = 'SELECT Count(complexity) AS moderate FROM 'SELECT @SQL1 = @SQL1 + @TableNameSELECT @SQL1 = @SQL1 + ' WHERE len(complexity) = 8'Exec ( @SQL1)ReturnIs there a better way of doing this??tiaDave
View 2 Replies
View Related
May 7, 2008
Hi,
I'm looking for some sample code rather than having to re-invent the wheel.
I need to write an analysis services stored proc that will invoke a SQL stored prod in my DW dataabse to retrieve a list of client_id's. I then need to construct and return a set object. This AS stored proc will be referenced from a role.
I've read few things about using the Set object rather than StrToSet function but then the only way I see to create a Member orSet object from a literal is to build an Expresion object and call the CalculateMdxObject(null).ToSet() function. Is this not equivalent to MDX.StrToSet()?
Also, ideally I'd like to connect to my SQL db by accessing the connection string from the Data Source objects in my AS DB. So far I have not found a way to do this.
Some guidance and sample code would be much appreciated.
Thanks.
View 8 Replies
View Related
Oct 14, 2004
Hi all,
I'm gonna need some help with this one.
I have this stored procedure written up that basically builds a dataset by querying a bunch of tables using outer joins. Our problem now is that it seems it takes a while for the dataset to pull back across the network. We would hence like to filter that dataset by adding on to the query in the procedure dynamically. Heres the query from the proc below:
SELECTN_Client.Prefix,
IsNull(dbo.N_CLIENT.SURNAME, '') + ', ' + IsNull(dbo.N_CLIENT.FIRST_NAME, '') AS Client_FullName,
dbo.N_CLIENT.TITLE,
dbo.N_COMPANY.COMPANY_NAME,
dbo.N_BUSINESS_UNIT.BUSINESS_UNIT_NAME,
dbo.N_DIVISION.DIVISION_NAME,
dbo.N_REF_INDUSTRY.INDUSTRY_NAME,
dbo.N_CLIENT.DIRECT_PHONE,
dbo.N_CLIENT.EMAIL,
dbo.N_CLIENT.TIER_ID,
(SELECT COUNT(Client_ID)
FROM N_Alumni
WHERE N_Alumni.Client_ID = N_Client.Client_ID) AS Alumni,
(SELECT COUNT(Client_ID)
FROM N_XREF_Client_Activity
WHERE N_XREF_Client_Activity.Client_ID = N_Client.Client_ID AND Activity_ID = 1) AS SandB,
(SELECT BAH_EMP_NID
FROM N_XREF_Client_Activity
WHERE N_XREF_Client_Activity.Client_ID = N_Client.Client_ID AND Activity_ID = 1) AS SandBMailer,
(SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'MM' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS MMEMPNID,
dbo.N_CLIENT.SURNAME AS Client_Surname,
dbo.N_CLIENT.FIRST_NAME,
dbo.N_CLIENT.FIRST_NAME AS Client_FirstName,
dbo.N_CLIENT.COMPANY_ID,
dbo.N_CLIENT.DIVISION_ID,
dbo.N_CLIENT.BUSINESS_UNIT_ID,
dbo.N_COMPANY.GROUP_ID,
dbo.N_CLIENT.COUNTRY,
dbo.N_GROUP.GROUP_NAME,
dbo.N_CLIENT.CLIENT_ID,
(SELECT IsNull(N_Vw_Client_BAH_Contact.First_Name, '') + ' ' + IsNull(N_Vw_Client_BAH_Contact.Surname, '')
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS PCFullName,
(SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS PCEMPNID,
(SELECT NMT_Practice_Code
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS NMT_Practice_Code,
(SELECT NMT_Practice_Name
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS NMT_Practice_Name,
#returnTable.AddlFullName,
#returnTable.AddlEMPNID,
#returnTable.FunctionID as Function_ID,
#returnTable.FunctionName as Function_Name,
(SELECT IsNull(N_Vw_Client_BAH_Contact.First_Name, '') + ' ' + IsNull(N_Vw_Client_BAH_Contact.Surname, '')
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'CSO' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS CSOFullName,
(SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'CSO' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS CSOEMPNID,
ISNULL(dbo.N_CLIENT.ARCHIVE_FLAG, 'N') AS Archive_Flag,
dbo.N_COMPANY.TARGET_COMPANY_FLAG,
dbo.N_COMPANY.INDUSTRY_ID,
N_Client.Address1,
N_Client.Address2,
N_Client.Address3,
N_Client.Address4,
N_Client.Address5,
N_Client.City,
N_Client.State,
N_Client.Postal_Code,
N_Client.Country,
N_Client.Region,
N_Client.Office_Code,
N_Client.Broderick_Target_Flag
FROMdbo.N_CLIENT
INNER JOIN
dbo.N_COMPANY ON dbo.N_CLIENT.COMPANY_ID = dbo.N_COMPANY.COMPANY_ID
LEFT OUTER JOIN
dbo.N_GROUP ON dbo.N_COMPANY.GROUP_ID = dbo.N_GROUP.GROUP_ID
LEFT OUTER JOIN
dbo.N_REF_INDUSTRY ON dbo.N_COMPANY.INDUSTRY_ID = dbo.N_REF_INDUSTRY.INDUSTRY_ID
LEFT OUTER JOIN
dbo.N_DIVISION ON dbo.N_DIVISION.DIVISION_ID = dbo.N_CLIENT.DIVISION_ID
LEFT OUTER JOIN
#returnTable ON #returnTable.CLIENT_ID = dbo.N_CLIENT.CLIENT_ID
LEFT OUTER JOIN
dbo.N_BUSINESS_UNIT ON dbo.N_CLIENT.BUSINESS_UNIT_ID = dbo.N_BUSINESS_UNIT.BUSINESS_UNIT_ID
ORDER BY N_Client.client_id
Where upper(title) like '%parameter_value%'
and company_id = 'parameter_value'
and Nmt_practice_code = 'parameter_value' ...............and so on
What we would like to do is to add 15 (where some may be null) input parameters to the definition of the query and then somehow (where the parameter is not null), dynamically add that parameter to the WHERE clause of the query illustrated in italics above. The bold print are examples of 3 of the 15 parameters to be passed into the query by the proc, so basically
title, company_id,Nmt_practice_code would be the 3 parameters being passed into this proc.
So in other words if 9 parameters out of the 15 are passed into the proc, we would like those 9 parameters to be added/built dynamically onto the SQL Query as 9 predicates. I hope I have been clear. Does anyone have any experience with this??? Help!!
Thanks
View 2 Replies
View Related
Oct 30, 2007
Table 1
First_Name
Middle_Name
Surname
John
Ian
Lennon
Mike
Buffalo
Tyson
Tom
Finney
Jones
Table 2
ID
F
M
S
DOB
1
Athony
Harold
Wilson
24/4/67
2
Margaret
Betty
Thathcer
1/1/1808
3
John
Ian
Lennon
2/2/1979
4
Mike
Buffalo
Tyson
3/4/04
5
Tom
Finney
Jones
1/1/2000
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.
View 17 Replies
View Related
Feb 5, 2008
A
ID
Name
1
Joe
2
Fred
3
Ian
4
Bill
B
ID
1
4
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)
select i.id
from inserted i
where
--specific USER
View 9 Replies
View Related
Jul 20, 2015
I wanted to execute a proc whenever a new login or a login is droped is created on the server. I was trying for CREATE_LOGIN in the following code but not able to capture the loginname and pass it to the proc.
Server object Trigger:-
USE [master]
GO
/****** Object: DdlTrigger [LoginCreateTrigger] Script Date: 7/20/2015 3:41:06 AM ******/
SET ANSI_NULLS ON
GO
[code]...
View 3 Replies
View Related
Mar 28, 2008
s it poaaible? as if so how can i trigger a job from a stored procedure?
View 2 Replies
View Related
Nov 29, 2007
I am fairly new to using triggers and stored procs in SQL Server and could use some help.
Scenario:
I have a table on SQL Server database running on a Windows server (of course) that contains information about articles. A second server in the shop is set up as a LAMP (Linux, Apache, MySQL, PHP) box. What I would like to do is any time the SQL server table is updated or a record is added, tables in MySQL would then be updated to reflect the changes on the first server. My thought is to try and do this with triggers and a stored proc.
Questions:
1) Assuming MySQL can be accessed through ODBC, can an ODBC connection be set up inside a stored proc or trigger, or would this have to be done through the Windows ODBC Data Sources tool in the control panel?
2) Can a Stored Proc be used in a trigger?
3) Can a Stored Proc call an outside function, such as an API call for third party software?
Thanks in advance.
View 1 Replies
View Related
Mar 23, 2007
--------------------------------------------------------------------------------
I want to fire a trigger which calls a stored proc, passing the name of the table the trigger is defined on to the proc without having to hardcode.
Can it be done ?
trigger x on table y
for update
as
declare @table_name
select @table_name = get underlying table_name 'y' from somewhere
exec stored proc (@table_name)
where do I find the name of the table the current trigger is defined on ?
thanks
View 3 Replies
View Related
Sep 17, 2007
I am writing a SQL 2000 stored procedure which uses an €˜EXEC @sqlString€™ statement. The @sqlString is generated at runtime. I want to give as few permissions as possible and currently allow users to access the database tables using only the stored procedures provided. However, with €˜Exec€™ I discover that I need to grant permissions on the actual tables to the users or groups. I would like to avoid this. I would also prefer not having to maintain a separate user with table level permissions and hardcoding the stored procedure with these details.
Is there anyway for me to dynamically generate the required SQL statement within my stored procedure and let SQL know that this stored procedure is allowed to select whatever tables it wants to without having to define permissions on the tables?
View 1 Replies
View Related
Sep 17, 2004
Is it possible to call an external web service from a SQL Server trigger or stored procedure?
View 6 Replies
View Related
Dec 7, 2004
Hello SQL Server programming gurus:
I am trying to create a trigger that fires after a user logon and logoff and does the following:
creates a new user
deletes data from old temp table
Then I need to create a stored procedure that executes dynamically to
drop old users
remove the old user account access
We are running SQL Server 2000.
Since I am new to T-SQL programming could anyone help point me in the right direction? Can I write dynamic SQL in a trigger to do these things?
View 1 Replies
View Related
Jul 23, 2005
I feel like I'm missing something obvious here, but I'm stumped...I have a stored procedure with code that looks like:INSERT INTO MyTableA ( ...fields... ) VALUES (...values...)IF (@@ERROR <> 0)BEGINROLLBACK TRANSACTION;RAISERROR('An error occurred in the stored proc.', 16, 1);RETURN(1);END--FORCING AN ERROR AT THE END FOR TESTING PURPOSESRAISERROR('Proc Successful',16,1)On MyTableA, there is a trigger that loops through the inserted data andstops the insert in certain circumstances, returning an error:IF (some criteria)BEGINROLLBACKRAISERROR('An error occurred in the trigger.',16,1)RETURNENDWhen I call the stored procedure from VB (connecting via RDO) witherror-causing data, the trigger successfully stops the insert, and adds thetrigger-error-msg to the errors collection, but it does NOT seem to createan error situation back in the stored procedure. The procedure finishes upwith the "Proc Successful" message, so that when I iterate through theerrors collection back in VB, I have "Proc Successful" followed by "An erroroccurred in the trigger."Is there some way I'm not finding to have the calling procedure recognizethat a raiserror occurred in the trigger and behave appropriately for anerror situation?Jen
View 1 Replies
View Related
May 7, 2008
I have a database trigger that is set to call a CLR trigger/stored proc when a certain field in a table is updated. The issue is that if i execute the stored proc manually in enterprise studio, it works perfectly but the same call made through the trigger does not go through. A few more details -
I have CLR integration enabled on the sql server.
The dbo has UNSAFE ASSEMBLY rights
I have the both the assembly and the serialized dll imported in the database.
Here's the definition of the stored proc -
CREATE PROCEDURE [dbo].[WriteXMLNotification]
@TaskID [nvarchar](20)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [DataInterfaceWebServices].[TaskUpdateXMLWriter.WriteXMLNotification].[run]
and the trigger -
CREATE TRIGGER [dbo].[tr_Task_U]
ON [dbo].[_Task]
FOR UPDATE, INSERT AS
IF UPDATE (TaskType_Status) OR UPDATE (TaskType) OR UPDATE (TaskType_SubType1)
BEGIN
SET NOCOUNT ON;
DECLARE @status AS INT
DECLARE @taskType AS INT
DECLARE @taskSubType AS INT
DECLARE @taskID as sysname
DECLARE @cmd as sysname
DECLARE @parentTask as sysname
DECLARE @NotificationXMLTaskID as sysname
SELECT @status = [TaskType_Status] FROM inserted
SELECT @taskType = [TaskType] FROM inserted
SELECT @taskSubType = [TaskType_SubType1] FROM inserted
SELECT @taskID = [TaskID] FROM inserted
SELECT @parentTask = [Parent_TaskID] FROM inserted
SELECT @NotificationXMLTaskID = [MCCTaskID] FROM _TaskNotificationXML WHERE [MCCTaskID] = @parentTask
IF (@status = 2602) AND (@taskType = 2282) AND (@taskSubType = 19500)
BEGIN
exec WriteXMLNotification @taskID;
END
ELSE IF (@taskType = 2285) AND (@parentTask IS NOT NULL) AND (@NotificationXMLTaskID IS NOT NULL)
BEGIN
exec WriteXMLNotification @parentTask;
END
END
I stepped into the trigger and it seems to execute the line " exec WriteXMLNotification @taskID;" but nothing happens, but if I run that same line manually, it works. Could it be that the impersonation by the EXECUTE AS clause is causing it to fail?
Please advise!
Thanks in Advance,
-Mihir Sonalkar.
View 1 Replies
View Related
Jan 14, 2008
This problem is being seen on SQL 2005 SP2 + cumulative update 4
I am currently successfully using the output clause of an insert statement to return the identity values for inserted rows into a table variable
I now need to add an "instead of insert" trigger to the table that is the subject of the insert.
As soon as I add the "instead of insert" trigger, the output clause on the insert statement does not return any data - although the insert completes successfully. As a result I am not able to obtain the identities of the inserted rows
Note that @@identity would return the correct value in the test repro below - but this is not a viable option as the table in question will be merge replicated and @@identity will return the identity value of a replication metadata table rather than the identity of the row inserted into my_table
Note also that in the test repro, the "instead of insert" trigger actually does nothing apart from the default insert, but the real world trigger has additional code.
To run the repro below - select each of the sections below in turn and execute them
1) Create the table
2) Create the trigger
3) Do the insert - note that table variable contains a row with column value zero - it should contain the @@identity value
4) Drop the trigger
5) Re-run the insert from 3) - note that table variable is now correctly populated with the @@identity value in the row
I need the behaviour to be correct when the trigger is present
Any thoughts would be much appreciated
aero1
/************************************************
1) - Create the table
************************************************/
CREATE TABLE [dbo].[my_table](
[my_table_id] [bigint] IDENTITY(1,1) NOT NULL,
[forename] [varchar](100) NULL,
[surname] [varchar](50) NULL,
CONSTRAINT [pk_my_table] PRIMARY KEY NONCLUSTERED
(
[my_table_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 70) ON [PRIMARY]
)
GO
/************************************************
2) - Create the trigger
************************************************/
CREATE TRIGGER [dbo].[trig_my_table__instead_insert] ON [dbo].[my_table]
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO my_table
(
forename,
surname)
SELECT
forename,
surname
FROM inserted
END
/************************************************
3) - Do the insert
************************************************/
DECLARE @my_insert TABLE( my_table_id bigint )
declare @forename VARCHAR(100)
declare @surname VARCHAR(50)
set @forename = N'john'
set @surname = N'smith'
INSERT INTO my_table (
forename
, surname
)
OUTPUT inserted.my_table_id INTO @my_insert
VALUES( @forename
, @surname
)
select @@identity -- expect this value in @my_insert table
select * from @my_insert -- OK value without trigger - zero with trigger
/************************************************
4) - Drop the trigger
************************************************/
drop trigger [dbo].[trig_my_table__instead_insert]
go
/************************************************
5) - Re-run insert from 3)
************************************************/
-- @my_insert now contains row expected with identity of inserted row
-- i.e. OK
View 5 Replies
View Related
May 10, 2008
How to create a dynamic trigger for a dynamic table?
View 3 Replies
View Related
Mar 1, 2004
Hi
I am trying to use multiple insert for a table T1 to add multiple rows.
Ti has trigger for insert to add or update multiple rows in Table T2.
When I provide multiple insert SQL then only first insert works while rest insert statements does not work
Anybody have any idea about why only one insert works for T1
Thanks
View 10 Replies
View Related
Feb 18, 2008
I writing a unit test which has one stored proc calling data from another stored proc. Each time I run dbo.ut_wbTestxxxxReturns_EntityTest I get a severe uncatchable error...most common cause is a trigger error. I have checked and rechecked the columns in both of the temp tables created. Any ideas as to why the error is occurring?
--Table being called.
ALTER PROCEDURE dbo.wbGetxxxxxUserReturns
@nxxxxtyId smallint,
@sxxxxxxxxUser varchar(32),
@sxxxxName varchar(32)
AS
SET NOCOUNT ON
CREATE TABLE #Scorecard_Returns
(
NAME_COL varchar(64),
ACCT_ID int,
ACCT_NUMBER varchar(10),
ENTITY_ID smallint,
NAME varchar(100),
ID int,
NUM_ACCOUNT int,
A_OFFICER varchar(30),
I_OFFICER varchar(30),
B_CODE varchar(30),
I_OBJ varchar(03),
LAST_MONTH real,
LAST_3MONTHS real,
IS int
)
ALTER PROCEDURE dbo.ut_wbTestxxxxReturns_EntityTest
AS
SET NOCOUNT ON
CREATE TABLE #Scorecard_Returns
(
NAME_COL varchar(64),
ACCT_ID int,
ACCT_NUMBER varchar(10),
ENTITY_ID smallint,
NAME varchar(100),
ID int,
NUM_ACCOUNT int,
A_OFFICER varchar(30),
I_OFFICER varchar(30),
B_CODE varchar(30),
I_OBJ varchar(03),
LAST_MONTH real,
LAST_3MONTHS real,
IS int
)
INSERT #Scorecard_Returns(
NAME_COL ,
ACCT_ID
ACCT_NUMBER ,
ENTITY_ID,
NAME,
ID,
NUM_ACCOUNT,
A_OFFICER,
I_OFFICER,
B_CODE,
I_OBJ ,
LAST_MONTH
LAST_3MONTHS,
IS
)
EXEC ISI_WEB_DATA.dbo.wbGetxxxxxcardUserReturns
@nId = 1,
@sSUser = 'SELECTED USER',
@sUName = 'VALID USER'
View 4 Replies
View Related
Jul 23, 2005
Hi AllI have a question about generating dynmamicly If Update() statement in atrigger..in My db, there is a table that holds some column names of an another table.for example;Columns Table-A: Col1, Col2, Col3, Col4,Col5Table-B: Col2, Col5 (The selected columns of Table A)Then, in the Trigger of Table-A I use;Select name from syscolumns where id=object_id('Table-A')fetch next from TableA_Cursor into @strColNamethen, I used a statement like this..if UPDATE(' + @strColName + ')But it gives "incorrect syntax" error..How can I write this line?Thanks alot in advance...--Message posted via http://www.sqlmonster.com
View 1 Replies
View Related
Feb 13, 2008
Hi!
I have an integration code write in T-SQL. It´s a TRIGGER that when some data is INSERTED on a specific table, verify the first caracter of a nvarchar on the column named "idCli", and depending on their value, call one specific stored procedure that will execute some data modifications to fit on other table on a diferent database.
Each client of mine can have only one table that start the trigger on APP1, but can have many instances of SQL for different codes.
Until now, what we do is:
Find how many different databases (and their names) a specific client have to APP2 and write a specific stored procedure for each database, using the names (that are always different...). We use a template of course, but this don't change the fact the we must correct many times the database name on the different stored procedures.
This increse the time and chance of errors on installing the system.
The first way we think for solve this question is using dinamic sql, like this code:
Code Snippet
CREATE TRIGGER T01
ON [dbo].[table1]
FOR INSERT, UPDATE
AS
-- some code that put values in @v1 and @V2...
IF @v1 = 1
EXEC fct ('DB1..Tabela1', @V2)
ELSE
EXEC fct ('DB2..Tabela1', @V2)
GO
CREATE PROCEDURE fct (@table_name nvarchar(50), @valor int)
AS
EXEC ('INSERT INTO '+@table_name+' (valor) VALUES ('+@valor+')')
GO
This type of code has the advantege (we think) to permit us change only the TRIGGER, and use always the same number of procedures on install.
Is there any security problem to do this type of code?
Even if the @table_name and @valor are determined by the program?
In case of yes, how can I do something like this, or, if this is not possible, how can I "automate" the creation of the procedures with a variable number of choices (like 2 different tables for client A, 5 for client B, etc)?
Thanks in advance
View 8 Replies
View Related
Jul 17, 2001
Hello all.
First of all, I've been a reader of swynk.com for quite sometime now, and I'd like to say 'thank you' to everyone who contributes.
Today, I'm the town moron.. haha I'm having issues with column level constraints. I have a varchar(50) where I want to keep *,=,#,/, .. etc, OUT OF the value input. I don't want to strip them. I simply want for sql to throw an error if the insert contains those (and other characters). The only characters that I want in the column are A-Z and 0-9. However, it's not a set number of characters per insert. It always varies... There has to be an easier way to do this than creating a constraint for every possibilty... Any help would be greatly appreciated.
tia,
Jeremy
View 1 Replies
View Related
