Create And Execute Trigger In C#
May 31, 2008
Hi all
I've Created a Trigger statement and tried to execute this using ExecuteNonQuery.but it shows the following error
Incorrect syntax near 'GO'.
'CREATE TRIGGER' must be the first statement in a query batch.
if i start with Create Trigger statement it show "Incorrect Syntax near Create Trigger".
the following is the trigger statement which i've generated in C#
Can anyone help me?
thanks in advance
sri
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'SampleTrigger' AND type = 'TR')
DROP TRIGGER SampleTrigger
GO
CREATE TRIGGER SampleTrigger
ON dbo.sample
AFTER INSERT
AS begin
SET NOCOUNT ON
DECLARE @RID as int
DECLARE @email AS nvarchar(50)
SELECT @email= i.email from inserted i
DECLARE @Name AS nvarchar(50)
SELECT @Name= i.Name from inserted i
DECLARE @Address AS nvarchar(50)
SELECT @Address= i.Address from inserted i
insert into Register(ServerName,DatabaseName,TableName) values('Sample','SamDatabase','SamTable')
SELECT @RID = @@Identity
insert into TableFields(RID,FieldName,FieldValue) values(@RID ,'Name',@Name)
insert into TableFields(RID,FieldName,FieldValue) values(@RID ,'Address',@Address)
insert into TableFields(RID,FieldName,FieldValue) values(@RID ,'email',@email)
end
View 1 Replies
ADVERTISEMENT
May 9, 2013
I am situation, where we have a table named as Project, columns for the table as follows:
Code:
------------------------------------------
ID | ClientCode | ProjectName
------------------------------------------
1 | AAA | Dubai Airport Phase I
2 | AAA | Dubai Airport Phase II
3 | ARC | Salala
4 | MIZ | UMBC Building
------------------------------------------
Now my task was, whenever a project name and other details being created, then a Folder will be created in a server itself in the path E:ProjectFolder in following way:
E:ProjectFolderAAAAAA1
E:ProjectFolderAAAAAA2
E:ProjectFolderARCARC3
E:ProjectFolderMIZMIZ4
You can see here Folder and sub-folder is being created with that following project - client code & ID
I used following trigger to do the same:
Code:
CREATE TRIGGER [dbo].[CreateFolderName]
ON [dbo].[Project]
after INSERT
AS
SET NOCOUNT ON
BEGIN
declare @chkdirectory as nvarchar(4000), @folderName varchar(100), @mainfolderName varchar(100)
declare @folder_exists as int
SET @mainfolderName = (SELECT ClientCode AS Project FROM INSERTED)
SET @folderName = (SELECT (ClientCode + cast(ID as varchar(10))) AS Project FROM INSERTED)
set @chkdirectory = 'E:ProjectFolder' + @mainfolderName + '' + @folderName
[code].....
This worked like a charm, now my next task is using same trigger, I have to create a BAT file inside that SubFolder - T-SQL for creation of BAT File as follows:
Code:
DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)
SET @FileName = REPLACE('E:ProjectFolder[red](select ClientCode from INSERTED)[/red][red](select ClientCode + cast(ID as varchar(10)) from INSERTED)[/red]xcopy_'+ (SELECT cast(ID as varchar(10)) FROM INSERTED) +'.bat','/','-')
SET @bcpCommand = 'bcp "[red]SELECT 'xcopy "E:ProjectFolder' + clientCode + '" "10.0.0.35ProjectFolder" /T /E /I' FROM INSERTED[/red]" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U SQLServerUsername -P SQLServerPassword -c'
EXEC master..xp_cmdshell @bcpCommand
Here I am not understanding how to insert the above T-SQL in the Trigger as well as the above T-SQL is not right, what's wrong in this?
Last query that will be included in the trigger is to execute the newly created bat file.
View 1 Replies
View Related
Mar 3, 2008
I have trigger, but not execute somedata because insert few row in every second. I use java to insert data to SQL server 2005. Data inserted to a table but not executing trigger for some data.
For example 100 data every second inserted to a table.
If insert data one by one to a table trigger fires success.
Please Help me.
View 1 Replies
View Related
Mar 18, 2008
how to create new CLR trigger from existing T-Sql Trigger Thanks in advance
View 3 Replies
View Related
Jun 20, 2003
I have a Stored Procedure that inserts and updates various information to various tables. I have a Begin Tran and Commit Tran at the beginning and end of my procedure.
Now on one of these tables that I'm inserting I want to put an After Insert Trigger.
When will the Trigger execute?
Will it execute when the actual Insert Into statement is ran? If so, what happens if the Stored Procedure bombs further down the code? Does the Rollback affect the changes in other Tables that the Trigger has made?
Or will it execute at the commit? Will the inserted tables still hold my newly inserted rolls from the Triggered Table after several other insertions and updates?
I hope all of this makes some sort of sense.
Thanks in advance,
Tim
View 2 Replies
View Related
Apr 28, 2004
Hi,
is there a way I can execute a DTS from a trigger without using xp_cmdshell?
I prefer not to open this procedure to the programmers here, and I'm looking for a workaround.
any suggestions?
View 1 Replies
View Related
Feb 13, 2008
I want to create a Trigger that will run every 5 minutes.
This trigger would actually run a Stored Procedure named say "SP_SetRooms".
This SP will in turn run and andupdate the rooms table.
Would anyone know how to help me get started on creating a Trigger with the info I've provided?
Thank you,
Dharmendra parihar
View 4 Replies
View Related
Nov 19, 2007
hello all
actually there is a problem i want to execute a trigger at very first day of every month,so that some records of a table can be updated at very first day of every month,how it will be executed automatically at every first day of month
View 12 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
Apr 1, 2008
I have never dealt with triggers before, I was wondering if it is possible to create a trigger that executes a script which transfers data from table1 to table2, upon table1 recieving new records.
I have the script written to transfer the data, I just dont really know the syntax of trigger creation. Any input would be great, thanks.
View 1 Replies
View Related
May 8, 2006
Hi,We have been using ADO and the AddNew method for a long time as a meansto add records to the database. It has always worked fine - no problem.But - we recently started using INSERT triggers that simply call a fewstored procs (they're actually SSNS stored procs that send new eventinfo to notification services). Anyway, these triggers do not seem tofire at all! If I execute an insert command manually from QueryAnalyser, no problems. But the trigger does not fire at all from myapplication!Does anyone know why this could be? For info, my connection string usedby the ADO connection object looks like this: Provider=SQLOLEDB.1;DataSource=XXX;Initial Catalog=YYYAnd my AddRecord ADO code looks like this:With rs.Open sSQL, ConnectionString, adOpenKeyset, adLockOptimistic,adCmdTable And adExecuteNoRecords.AddNewAm I mnissing something obvious here? Any help appreciated!
View 1 Replies
View Related
Dec 26, 2007
Hi,
Is it possible to run trigger from a stored procedure?
View 1 Replies
View Related
Feb 22, 2008
Hi
I have a situation below
Table : tblStatus
Column1 : Event_Desc
Column2 : Flag
Once the flag is set to "T", I want to trigger a SSIS package named "PerformAction.dtsx"
How to write a table trigger for this.
With Regards,
Madhu
View 7 Replies
View Related
Jul 18, 2007
Hi,
I have a 'Execute SQL Task' in my 'control flow', my 'Execute SQL Task' will return a value which I am assigning to a variable. Based on the value of the variable, I need to control my other flows. If the variable's value is 1 then I should invoke a dataflow, else I should write a failure error message in event viewer. Please could someone provide some inputs on how this can be done.
'Execute SQL Task' ----->value 1 ------>data flow to be executed
'Execute SQL Task' ----->value !=1 ------> write some error message in the event viewer and no tasks should be executed after that.
Thanks
raj
View 1 Replies
View Related
Jun 24, 2015
Suppose you have a simple DML "execute as..." trigger that looks like this:
CREATE TRIGGER dbo.myTrigger
 ON dbo.myTable
 WITH Execute As Owner
 AFTER INSERT
AS
[code]...
which runs as "owner" so that the user inserting into "myTable" won't have the privs to edit the audit table.How does one capture the identity of the user whose insert statement caused the trigger to fire? I've tried "current_user" and "system_user" without success when "execute as ..." is used: they each return a value not related to the identity of the user running the original insert.without an "execute as" clause, what prevents a user from adding to or editing the audit table directly?
View 6 Replies
View Related
May 22, 2001
Hi,
I'm using SQL 7.0 SP2 on NT SP6a and have a quick question.
I want to create a scheduled job to execute several DTS packages (all located on the same server). I can use Enterprise Manager to schedule an individual package but the command line of the step has something like:
DTSRun /~S 0x26CE410E3EFF5E8B738DC1C3527EEA1A /~N 0xCAAF8584582AA98C09F5CF132CB0BB5F296D1E9053DD6526 717A53AD4134C3463615FEF20330386927242B6CB7070036 /E
Is using the DTSRUN utility the only way to create several steps in a job to run DTS packages ?
Any help appreciated.
Thanks,
Tim
View 1 Replies
View Related
Jul 25, 2006
I have a little application that I have designed where I need to be able to execute create table and create function comands against the database.
It seems that it does not like my sql file. Does anyone know of a different method of doing this?
Error message
Line 2: Incorrect syntax near 'GO'.
Line 4: Incorrect syntax near 'GO'.
Line 8: Incorrect syntax near 'GO'.
'CREATE FUNCTION' must be the first statement in a query batch.
Must declare the variable '@usb'.
Must declare the variable '@usb'.
Must declare the variable '@i'.
A RETURN statement with a return value cannot be used in this context.
Line 89: Incorrect syntax near 'GO'.
Line 91: Incorrect syntax near 'GO'.
Line 94: Incorrect syntax near 'GO'.
Protected Sub Install()
Dim err As String = ""
While err.Length < 1
' Dim your StreamReader
Dim TextFileStream As System.IO.TextReader
'Load the textfile into the stream
TextFileStream = System.IO.File.OpenText(Request.PhysicalApplicationPath & "Scripts .sql")
'Read to the end of the file into a String variable.
executesql(TextFileStream.ReadToEnd, err)
err = "Susscessful"
End While
If err = "Susscessful" Then
Response.Redirect("Default.aspx")
Else
Me.lblError.Text = err
End If
End Sub
Private Function executesql(ByVal s As String, ByRef err As String) As Boolean
Try
Dim conn As New Data.SqlClient.SqlConnection(GenConString())
Dim cmd As New Data.SqlClient.SqlCommand(s, conn)
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
Return True
Catch ex As Exception
err = ex.Message.ToString
Return False
End Try
End Function
Example sql file
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MyFunc]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MyFunc]
GO
CREATE FUNCTION [dbo].[MyFunc]
(
-- Add the parameters for the function here
)
RETURNS varchar(1000)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result varchar(1000)
-- Add the T-SQL statements to compute the return value here
-- Do something here
-- Return the result of the function
RETURN @Result
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
View 1 Replies
View Related
Jun 10, 1999
I am having trouble getting a job step to execute an executable file. One of the options for a job step is to enter an operating system command. I have tried entering the following operating system command to execute a file:
'start c:myfile.exe'
For some reason this doesn't work. How can I get SQL Server to execute an executable file?
View 3 Replies
View Related
Sep 20, 2007
Hi
I am supposed to execute the script provided to create tables in a database...but I am not getting it from where I run those scripts ?
The script has commands to create tables...
From where do i execute those ?
View 3 Replies
View Related
May 6, 2008
I am creating a login with a user in one database. The id has to
create procedures in the dbo schema, execute them, and grant execute on them to other users.
BOL says to
GRANT ALTER ON SCHEMA::DBO TO username ;
GRANT CREATE PROCEDURE TO username ;
I did that. User can now create procedures, but cannot execute what he creates. And cannot grant execute on the sp to anybody.
I don't want to give this id a lot of priviledges.
View 16 Replies
View Related
Sep 20, 2007
Hi
I am supposed to execute the script provided to create tables in a database...but I am not getting it from where I run those scripts ?
The script has commands to create tables...
From where do i execute those ?
View 3 Replies
View Related
May 10, 2007
hello,
I am loading data from one DB to another. I wish to load them every day at night. I know to do that, I must write a script. But how to do it? I don't know. Could someone help me?
thanks in advance
View 3 Replies
View Related
Sep 14, 2004
I have a table that has a number of data fields,I need to be able to capture datatime when the date field was entered or entered value changed.I was told I need to create a trigger on that table that contains all the fields.
I have seen the syntax for creating triggers, and read some documentation but I am still in the dark as how to create what I need to.
I was hoping to see if somebody had a similar example or an advice, anything is more than what I have at the moment.
CREATE TRIGGER NotifyDateFieldUpdates
ON RelocateeRemovalist
For INSERT, UPDATE, DELETE
AS
DECLARE @RemovalistNumber VARCHAR(200)
DECLARE @RelocateID INT
/*InspectionDate */
DECLARE getInsp CURSOR FOR SELECT RelocateID,RemovalistNumber
FROM INSERTED a LEFT JOIN DELETED b ON (a.RemovalistNumber=b.RemovalistNumber and a.RelocateID=b.RelocateID)
WHERE a.InspectionDate IS NOT NULL AND b.InspectionDate IS NULL
OPEN getInsp
FETCH NEXT FROM getInsp INTO @RelocateID, @RemovalistNumber
WHILE (@@FETCH_STATUS <> -1)
BEGIN
INSERT INTO RelocateeRemovalistFieldEntry(RElocateID, RemovalistID)SELECT
RelocateID,RemovalistID FROM INSERTED a LEFT JOIN RelocateeRemovalistFieldEntry b ON
(a.RelocateID=b.RelocateID AND a.RemovalistNumber=b.RemovalistNumber)WHERE b.RElocateID is null
UPDATE RelocateeRemovalistFieldEntry SET InspectionDateDateTime=GETDATE()
WHERE RelocateID=@RelocateID aND RemovalistNumber=@RemovalistNumber
FETCH NEXT FROM getInsp INTO @RelocateID, @RemovalistNumber
END
DEALLOCATE getInsp
GO
This is what I was able to come up with so far,but when i check the syntax it gives me an error "Ambiguous column name "RelocateID" and "Ambiguous column name "RemovalistNumber" I don't know what is it trying to tell me here and couldn't find much help.
Regards and thanks
View 8 Replies
View Related
May 9, 2008
i do have my 'Product' TABLE IN DATABASE 'ABC'
Product TABLE OUTPUT
PRODUCT_CODE PRODUCT_TYPE PRODUCT_DESCPRODUCT_ID PRODUCT_GROUP_CODE
6001 computer NULL ENVD14
6002 keyboard NULL ENVD14
6003 mouse NULL ENVD14
6004 cables NULL ENVD14
6005 processor NULL ENVD14
AND 'Product_Mst' TABLE IN DATABASE 'XYZ'
Product_Mst OUTPUT
PROD_CODE Prod_Ver PROD_TYPE PROD_DESC PROD_ID PROD_GRP_CODE
6001 0 computer NULL ENVD 14
6002 0 keyboard NULL ENVD 14
6003 0 mouse NULL ENVD 14
6004 0 cables NULL ENVD 14
6005 0 processor NULL ENVD 14
Now i want TO CREATE TRIGGER such that every updation in Product TABLE will UPDATE the appropriate record IN Product_Mst
FOR example IF i fire below query IN ABC Database
UPDATE Product
SET PRODUCT_DESC = 'Available' WHERE Product_Code = 6001
Then the OUTPUT OF the Product_Mst shoub be..
Product_Mst OUTPUT
PROD_CODE Prod_Ver PROD_TYPE PROD_DESC PROD_ID PROD_GRP_CODE
6001 0 computer NULL ENVD 14
6001 1 computer NULL ENVD 14
6002 0 keyboard NULL ENVD 14
6003 0 mouse NULL ENVD 14
6004 0 cables NULL ENVD 14
6005 0 processor NULL ENVD 14
Means i want to increment the version by 1 and Insert that records into Product_Mst Table at every updation.
I hope i am clear with my question.
Regards
Prashant Hirani
View 1 Replies
View Related
Jan 6, 2015
We have Tables like parent and Child Table.Like we have Child Table as Name AcademyContacts,In that we have Columns like
Guid(PK)Not Null,
AcademyId(FK), Not Null,
Name,Null
WorkPhone,Null
CellPhone,Null
Email Id,Null
Other.Null
Since we have given Null to ''Workphone'',''Cellphone '', ''Email ID''.when inserting the data into these table.if the particular columns are empty while inserting also the data will get populate into the table.And.I need is if these columns are ''Workphone'',''Cellphone'' , ''Email ID'' they cant insert the data into table.
Like it must trigger like ''Please enter atleast one of these ''Workphone'',''Cellphone'' , ''Email ID'' columns.
View 4 Replies
View Related
Jul 20, 2005
Hi everybody,How can I Update a field from another table by Trigger? Can someone sendme the statment to do it?I have a table called Clients with fields : ID_Clients, ClientAnd Another called Doc with fields : ID_Doc, ID_Clients, ClientThese tables are in different databases and I would like to esure theintegrity by add a Trigger to update in Doc´s table the field Clienteverytime it´s changed in the Client´s table.Thanks for Attetion.Leonardo Almeida*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Jul 20, 2005
Hii have 2 Tablefirst one : Customerwith 4 Fields : cst_no,cst_name,total_Debit,tot_creditsecond one : Transactionwith 5 Fields : Trns_no,Trns_Date,cst_no,debit,creditMY QUESTION:HOW TO CREATE TRIGGER FOR UPDATE TOT_DEBIT AND TOT_CREDIT FILEDS INCUSTOMER TABLE FROM Transaction TABLEThank you
View 4 Replies
View Related
Jan 10, 2008
Hi folks,
I have created a trigger that I can enter and works great from the sqlcmd terminal. When I try to submit the same query in a .NET project I get an error that "Create Trigger must be the first statement in a batch". The Create trigger is submitted by itself through the sqlcommand.executenonquery() method.
I am trying to create a database for a project but the only thing that I can't seem to get working is submitting this trigger.
Appreciate any help.
Dave
View 5 Replies
View Related
May 10, 2008
I am trying to create a trigger to help me get a the time duration but this is not working.
Code Snippet
CREATE TRIGGER Duratn
ON dbo.CONTACTS
AFTER INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON
UPDATE dbo.CONTACTS
SET Duratn = (DATEDIFF(CallStartTime) - DATEDIFF(CallFinishTime))
My table is as follows:
No Caller CallStartTime CallFinishTime Duratn
10000 John 10/05/2008 18:13:00 10/05/2008 18:14:00 NULL
View 13 Replies
View Related
Oct 4, 2007
What I was trying to use to create the trigger was the same code I would use on Sql Server Express:
cmd.CommandText = "CREATE Trigger [contactsLastUpdate] on [contacts] for Insert, Update " +
"AS " +
"Begin " +
"SET NOCOUNT ON " +
"Update t " +
"set syncLastUpdate = GetDate() " +
"From contacts t " +
"Join inserted i " +
"On t.id = i.id " +
"SET NOCOUNT OFF " +
"End"; +
But I get an error message:
"There was an error parsing the query. [ Token line number = 1,Token line offset = 8,Token in error = Trigger ]"
How do you guys create Triggers on SQL Server CE (2005)?
Thanks
View 3 Replies
View Related
Aug 27, 2002
In our development and test environments the developers need to create and execute stored procedures as dbo without having any other dbo permissions. If I place them in db_owner, they have too many permissions. Is there a way to address this situation?
I'm also curious how other companies address the subject of creating stored procedures in development and test environments. If I give developers create and execute permission in a database, all objects would be created as JohnDoe.storedprocedurename instead of dbo.storedprocedurename. Any help in this area is appreciated.
Dave
View 1 Replies
View Related
Jun 18, 2007
Hello All!
I am trying to create a trigger that upon insertion into my table an email will be sent to that that recipeinent with a image attached ( like a coupon)That comes from a different table, problem is, It will not allow me to send the email ( using xp_sendmail) with the coupon attached. I am using varbinary for the coupon and nvarchar for the rest to be sent, I get an error that Invaild operator for data type. operator equals add, type equals varchar.
Looks basically like this(This is my test tables):
CREATE TRIGGER EileenTest ON OrgCouponTestMainFOR InsertAS declare @emailaddress varchar(50)declare @body varchar(300)declare @fname varchar(50)declare @coupon varbinary(4000)
if update(emailaddress)begin
Select @emailaddress=(select EmailAddress from OrgCouponTestMain as str), @fname=(select EmailAddress from OrgCouponTestMain as str) @Coupon=(select OrgCoupon1 from OrgCouponTest2 as image)
SET @body= 'Thank you' +' '+ @fname +' '+ ',Here is the coupon you requested' +' ' + @couponexec master.dbo.xp_sendmail @recipients = @emailaddress, @subject = 'Coupon', @message = @bodyEND
View 6 Replies
View Related
Oct 21, 2004
I have the following
CREATE TRIGGER dbo.tgrCacheCustomers
ON dbo.Customers
FOR INSERT, UPDATE, DELETE
AS
EXEC sp_makewebtask 'C:DependencyFile.txt','SELECT top 1 CustomerId FROM customers'
and I get the following error that I dont understand:
Error 21037: [SQL-DMO] The name specified in the Text property's 'CREATE ...' statement must match the Name property, and must be followed by valid TSQL statements.
Any ideas someone?
View 2 Replies
View Related