Auto Create Trigger After Re-initialization Completed

Jan 5, 2006

Hi all,

Is it possible to create a trigger after creation of table during reinitialization? if so, how can I do that? Thanks in advance!

View 11 Replies

Create Procedure Or Trigger To Auto Generate String ID

Feb 20, 2004

Dear everyone,

I would like to create auto-generated "string" ID for any new record inserted in SQL Server 2000.

I have found some SQL Server 2000 book. But it does not cover how to create procedure or trigger to generate auto ID in the string format.

Could anyone know how to do that?? Thanks!!

From,

Roy

View 7 Replies View Related

Creating Trigger To Auto Set Create/modify Dates

Jul 20, 2005

Hi,I'm a newbie to sql server and this may be a really dumb question forsome you. I'm trying to find some examples of sql server triggers thatwill set columns (e.g. the created and modified date columns) if the rowis being inserted and set a column (e.g. just the modified date column)if the row is being updated.I know how to do this in oracle plsql. I would define it as a beforeinsert or update trigger and reference old and new instances of therecord. Does sql server have an equivalent? Is there a better way to dothis in sql server?Thanksericthis is what i do in oracle that i'm trying to do in sqlserver...CREATE OR REPLACE TRIGGER tr_temp_biubefore insert or updateon tempreferencing old as old new as newfor each rowbeginif inserting then:new.created_date := sysdate;end if;:new.modified_date := sysdate;end tr_temp_biu;

View 1 Replies View Related

SQL:Stmt Completed V SQL:Batch Completed

Apr 29, 2008

I'm troubleshooting a performance issue , Looking at Profiler - for the given statement, I'm getting the following figures , why would there be such a disparity between the figures. ? How can I go about finding out why there is such difference?

SQL:Stmt Completed:CPU = 31, Reads = 129 , Duration = 32
SQL:Batch Completed: CPU = 2531, Reads = 6087 , Duration = 2593

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

View 2 Replies View Related

Getting The Auto Value From Trigger

Jan 29, 2001

Hi all,

My requirement is to get the autoincrement column once a new row is inserted, we need the autoincrement value to update other tables, at present I am using an insert trigger in which I am extracting the autoincrement column from the 'inserted' table, but how far this work perfectly when multiple users insert simultaneously. Can any of you suggest me the best way to extract the actual value inserted.

Now the scenario is :

sp which insert a row
Begin tran
insert ...
select @returnKey = (select retkey from #temptab)
drop #temptab
Commit Tran

Trigger on insert

insert idcolumn into #temptab select autokey from inserted

If user A & B inserts row exactly at same time, will this method return the exact auto value what A and B have inserted to them respectively.

Thanks in Anticipation

Raj

View 1 Replies View Related

SQL Trigger And Auto Email

Oct 16, 2006

Hi,

I have a trigger for a table which stores email information generated from an ACCESS form. The trigger should send an auto email response to users who submitted an email to request for their password (we have forgetful users!). There is something wrong with my trigger because the auto email is sent out with a blank body... I will appreciate any advice!

Thank you!

My trigger:
CREATE TRIGGER tr_SendPassword ON PIPEmail
FOR INSERT
AS
DECLARE @Password varchar(100)
DECLARE @EmailAddress varchar(100)
DECLARE @message varchar(100)
IF (select count(*) from inserted) = 1
BEGIN
IF exists (SELECT * FROM inserted
WHERE Subject = 'Forgot my password')
BEGIN
select @Password = 'We received an email request from you for your password. Your password for SAR Search is: ' + UserRole.Password,
@EmailAddress = [User].Email
from UserRole
join inserted on UserRole.WindowsUser = inserted.WindowsUser
join [User] on [User].WindowsUser = UserRole.WindowsUser

exec master.dbo.xp_sendmail @recipients=@EmailAddress,
@subject='SAR Search password request',
@message=@Password
END
END

View 4 Replies View Related

Create Auto ID

Mar 4, 2008

I created a table with a field called myID. I set the data type as "uniqueidentifier", but it won't auto generate. How do I create a field to auto generate a unique number? I'm working with SQL Server 2000 and I'm creating the table from Enterprise Manager.

View 5 Replies View Related

How Do I Create An Auto Increment?

Apr 29, 2006

Hi all

I just got SQL server 2k5 installed and working....im just trying to figure out how to make an auto incremement column wiht an integer..

or has it been replaced with the "unique identifyer"

Abyss

View 4 Replies View Related

Create Auto Number

Apr 16, 2004

how can i create an auto number field in sql server 2000?

thanks kris

View 1 Replies View Related

Auto Create && Load In SQL Table

Apr 19, 2006

Hi does anyone know how to create a sql table and then import a list by just clicking on a button to call a procedure?CREATE TABLE clients(ClientID VARCHAR(5), ClientName VARCHAR(30), PRIMARY KEY (ClientID));LOAD DATA LOCAL INFILE 'C:/client.csv' INTO TABLE clientsLINES TERMINATED BY '
';

View 1 Replies View Related

Auto Create Statistics / Indexes

Sep 1, 2000

Hi everyone,

I know that statistics called _WA_... are created on tables when auto create statistics is set on a database. Is this an indication that queries against the table would perform better if indexes were created on the columns in question? (The tables I'm interested in optimising are used equally for transactional querying and reporting)

Thanks for any replies!

Les

View 1 Replies View Related

Auto Create History Tables And Triggers

May 30, 2007

For my company, we have made it a standard to create history tables and triggers for the majority of our production tables. I recently grew tired of consistently spending the time needed to create these tables and triggers so I invested some time in creating a script that would auto generate these.

We recently launched a project which required nearly 100 history tables & triggers to be created. This would have normally taken a good day or two to complete. However, with this script it took a near 10 seconds. Here are some details about the script.

The code below creates a stored procedure that receives two input parameters (@TableName & @CreateTrigger) and performs the following actions:

1) Queries system tables to retrieve table schema for @TableName parameter

2) Creates a History table ("History_" + @TableName) to mimic the original table, plus includes additional history columns.

3) If @CreateTrigger = 'Y' then it creates an Update/Delete trigger on the @TableName table, which is used to populate the History table.

/************************************************************************************************************
Created By: Bryan Massey
Created On: 3/11/2007
Comments: Stored proc performs the following actions:
1) Queries system tables to retrieve table schema for @TableName parameter
2) Creates a History table ("History_" + @TableName) to mimic the original table, plus include
additional history columns.
3) If @CreateTrigger = 'Y' then it creates an Update/Delete trigger on the @TableName table,
which is used to populate the History table.
******************************************* MODIFICATIONS **************************************************
MM/DD/YYYY - Modified By - Description of Changes
************************************************************************************************************/
CREATE PROCEDURE DBO.History_Bat_AutoGenerateHistoryTableAndTrigger
@TableName VARCHAR(200),
@CreateTrigger CHAR(1) = 'Y' -- optional parameter; defaults to "Y"
AS

DECLARE @SQLTable VARCHAR(8000), @SQLTrigger VARCHAR(8000), @FieldList VARCHAR(6000), @FirstField VARCHAR(200)
DECLARE @TAB CHAR(1), @CRLF CHAR(1), @SQL VARCHAR(1000), @Date VARCHAR(12)

SET @TAB = CHAR(9)
SET @CRLF = CHAR(13) + CHAR(10)
SET @Date = CONVERT(VARCHAR(12), GETDATE(), 101)
SET @FieldList = ''
SET @SQLTable = ''

DECLARE @TableDescr VARCHAR(500), @FieldName VARCHAR(100), @DataType VARCHAR(50)
DECLARE @FieldLength VARCHAR(10), @Precision VARCHAR(10), @Scale VARCHAR(10), @FieldDescr VARCHAR(500), @AllowNulls VARCHAR(1)

DECLARE CurHistoryTable CURSOR FOR

-- query system tables to get table schema
SELECT CONVERT(VARCHAR(500), SP2.value) AS TableDescription,
CONVERT(VARCHAR(100), SC.Name) AS FieldName, CONVERT(VARCHAR(50), ST.Name) AS DataType,
CONVERT(VARCHAR(10),SC.length) AS FieldLength, CONVERT(VARCHAR(10), SC.XPrec) AS FieldPrecision,
CONVERT(VARCHAR(10), SC.XScale) AS FieldScale,
CASE SC.IsNullable WHEN 1 THEN 'Y' ELSE 'N' END AS AllowNulls
FROM SysObjects SO
INNER JOIN SysColumns SC ON SO.ID = SC.ID
INNER JOIN SysTypes ST ON SC.xtype = ST.xtype
LEFT OUTER JOIN SysProperties SP ON SC.ID = SP.ID AND SC.ColID = SP.SmallID
LEFT OUTER JOIN SysProperties SP2 ON SC.ID = SP2.ID AND SP2.SmallID = 0
WHERE SO.xtype = 'u' AND SO.Name = @TableName
ORDER BY SO.[name], SC.ColOrder

OPEN CurHistoryTable

FETCH NEXT FROM CurHistoryTable INTO @TableDescr, @FieldName, @DataType,
@FieldLength, @Precision, @Scale, @AllowNulls

WHILE @@FETCH_STATUS = 0
BEGIN

-- create list of table columns
IF LEN(@FieldList) = 0
BEGIN
SET @FieldList = @FieldName
SET @FirstField = @FieldName
END
ELSE
BEGIN
SET @FieldList = @FieldList + ', ' + @FieldName
END

IF LEN(@SQLTable) = 0
BEGIN
SET @SQLTable = 'CREATE TABLE [DBO].[History_' + @TableName + '] (' + @CRLF
SET @SQLTable = @SQLTable + @TAB + '[History' + @FieldName + '] [INT] IDENTITY(1,1) NOT NULL,' + @CRLF
END

SET @SQLTable = @SQLTable + @TAB + '[' + @FieldName + '] ' + '[' + @DataType + ']'

IF UPPER(@DataType) IN ('CHAR', 'VARCHAR', 'NCHAR', 'NVARCHAR', 'BINARY')
BEGIN
SET @SQLTable = @SQLTable + '(' + @FieldLength + ')'
END
ELSE IF UPPER(@DataType) IN ('DECIMAL', 'NUMERIC')
BEGIN
SET @SQLTable = @SQLTable + '(' + @Precision + ', ' + @Scale + ')'
END

IF @AllowNulls = 'Y'
BEGIN
SET @SQLTable = @SQLTable + ' NULL'
END
ELSE
BEGIN
SET @SQLTable = @SQLTable + ' NOT NULL'
END

SET @SQLTable = @SQLTable + ',' + @CRLF

FETCH NEXT FROM CurHistoryTable INTO @TableDescr, @FieldName, @DataType,
@FieldLength, @Precision, @Scale, @AllowNulls
END

CLOSE CurHistoryTable
DEALLOCATE CurHistoryTable

-- finish history table script with standard history columns
SET @SQLTable = @SQLTable + @TAB + '[HistoryCreatedOn] [DATETIME] NULL,' + @CRLF
SET @SQLTable = @SQLTable + @TAB + '[HistoryCreatedByUserID] [SMALLINT] NULL,' + @CRLF

SET @SQLTable = @SQLTable + @TAB + '[HistoryCreatedByUserName] [VARCHAR](30) NULL,' + @CRLF
SET @SQLTable = @SQLTable + @TAB + '[HistoryAction] [CHAR](1) NOT NULL' + @CRLF
SET @SQLTable = @SQLTable + ' )'

PRINT @SQLTable

-- execute sql script to create history table
EXEC(@SQLTable)

IF @@ERROR <> 0
BEGIN
PRINT '******************** ERROR CREATING HISTORY TABLE FOR TABLE: ' + @TableName + ' **************************************'
RETURN -1
END

IF @CreateTrigger = 'Y'
BEGIN
-- create history trigger
SET @SQLTrigger = '/************************************************************************************************************' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'Created By: ' + SUSER_SNAME() + @CRLF
SET @SQLTrigger = @SQLTrigger + 'Created On: ' + @Date + @CRLF
SET @SQLTrigger = @SQLTrigger + 'Comments: Auto generated trigger' + @CRLF
SET @SQLTrigger = @SQLTrigger + '***********************************************************************************************/' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'CREATE TRIGGER [Trigger_' + @TableName + '_UpdateDelete] ON DBO.' + @TableName + @CRLF
SET @SQLTrigger = @SQLTrigger + 'FOR UPDATE, DELETE' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'AS' + @CRLF + @CRLF
SET @SQLTrigger = @SQLTrigger + 'DECLARE @Action CHAR(1)' + @CRLF + @CRLF
SET @SQLTrigger = @SQLTrigger + 'IF EXISTS (SELECT ' + @FirstField + ' FROM Inserted)' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'BEGIN' + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + 'SET @Action = ''U''' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'END' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'ELSE' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'BEGIN' + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + 'SET @Action = ''D''' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'END' + @CRLF + @CRLF
SET @SQLTrigger = @SQLTrigger + 'INSERT INTO History_' + @TableName + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + '(' + @FieldList + ', HistoryCreatedOn, HistoryCreatedByUserName, HistoryAction)' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'SELECT ' + @FieldList + ', GETDATE(), SUSER_SNAME(), @Action' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'FROM DELETED'

--PRINT @SQLTrigger

-- execute sql script to create update/delete trigger
EXEC(@SQLTrigger)

IF @@ERROR <> 0
BEGIN
PRINT '******************** ERROR CREATING HISTORY TRIGGER FOR TABLE: ' + @TableName + ' **************************************'
RETURN -1
END

END

View 13 Replies View Related

How To Create A View With An Auto Number Column?

Apr 9, 2008

I have a View created from 2 tables. How do I add an autoindex (0,1,2,3,..) to a new column?

View 8 Replies View Related

How To Create New CLR Trigger From Existing T-Sql Trigger

Mar 18, 2008

how to create new CLR trigger from existing T-Sql Trigger Thanks in advance

View 3 Replies View Related

SQL Server 2014 :: Create Auto Relationship Between Tables

May 12, 2014

I would like to create a auto relationship between tables.

Currently I am using Northwind DB with tables (Orders, OrderDetails, Customers)

Orders ( OrderId, Customerid)
OrderDetails(OrderId)
Customers(CustomerID)

Now, if the user wants to generate a relationship automatically based on SAME FIELD Names.

What is the approach?

View 9 Replies View Related

Returning Completed When Status = 1 And Not Completed When Status = 0

May 3, 2005

Returning "completed" when status = 1 and "not completed when status = 0

View 3 Replies View Related

Create Trigger Help?

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

Create Trigger Help

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

How To Create DML Trigger

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

Help Me Create This Trigger

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

HOW TO CREATE TRIGGER ?

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

Create A Trigger Through C#/.NET

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

Help To Create Trigger

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

Trigger. How To Create?

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

Help! Trying To Create A Trigger In SQL 2000...

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

Error On Create Trigger

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

Create Trigger On Sysobjects

Aug 16, 2000

Hi there,

Once in a while, I find down there are some missing objects in production database. Because we share the sa password with couple of developers, therefore, it's hard find down who did it. So, I try to create a trigger in sysobjects table to prevent this problem, however, I keep getting the error message "error 229: create trigger permission denied on object 'sysobjects'"
although I log in using sa. Can someone give me some suggestions how to prevent this from happening beside using trace profiler and also why do I get the denied message when create trigger on sysobject even with sa login.

Thanks in advance

View 1 Replies View Related

Create Audit Trigger

Nov 2, 2004

I need to create a simple audit trigger for a table with 12 columns. I need to determine which row was changed. Is there a simple way to do that. The table structure is
ID Integer(4)
barcode(25)
epc_tag(24)
bc_stop_flag(1)
reject_flag(1)
complete_flag(1)
hold_flag(1)
pe_1_flag
pe_2_flag
pe_3_flag
pe_4_flag
pe_5_flag

View 3 Replies View Related

Create Trigger Not Insert If

Sep 25, 2006

how can i Create a trigger to check if a value is NULL or = 0

i am trying :

CREATE TRIGGER [TR_User]
ON [User]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;

DELETE FROM User WHERE (Category = 0 OR Category IS NULL)
END

but in that way i dont CHECK the new inserted value directly, is it possible not to INSERT it if the value is NULL or = 0 ?

not to look all the table for each new line inserted

View 14 Replies View Related

Create An Update Trigger

May 8, 2008

Hi,
I have a table with somefields, here i will only mention on which i need to perform an action, possibly with the use of Trigger.

Fields = Active, inactiveDate
Active Field is of bit datatype mean conatins 1 or 0,
1 means the user is active, but when i change the active field to 0, and make the user inactive i want the date to be populated automatically to inactiveDate field when active field is changed to 0.

any help is much appreciated

NAUMAN

View 2 Replies View Related

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 View Related

Create Trigger - Do Users Need To Be Out?

Jul 6, 2006

I created and successfully tested a trigger on a test database. Now that Iwant to put this on a production system, the create trigger statement takesway too long to complete. I cancelled after a few minutes. The testtrigger took just a second to create. The test and production databases areidentical in design. Only difference is that there are users in theproduction system.Any ideas?Thanks

View 2 Replies View Related

Create A File Using A SQL DB Trigger

Jul 20, 2005

Is there a way to create a text file (such as a Windows Notepad file)by using a trigger on a table? What I want to do is to send a row ofinformation to a table where the table: tblFileData has only onecolumn: txtOutputI want to use the DB front end (MS Access) to send the text string tothe SQL backend, then have the SQL Server create a file to a path,such as F:/myfiledate.txt that holds the text in txtOutput, then thetrigger deletes the row in tblFileData.Can this be done easily?Any help is appreciated

View 9 Replies View Related