creating triggers in transactional replication on the subscriber side

Hi all

i have setup default transactional replication using locat distributor scheme. I need to create triggers on tables at subscriber side. Can this be done using transaction replication?

Thanks,

Arslan.

are these triggers defined at the publisher, or were you wanting to create new triggers? Assuming the latter, you can put them in a SQL file and reference @.post_snapshot_script in sp_addpublication.|||

If what you would like to do is to replicate triggers defined on the published table, you can take a look at @.schema_option 0x100 in sp_addarticle. If you are using UI, there is also a "copy user triggers" option in article properties dialog.

Peng

Creating Triggers

Can someone please help me with the following problem:

SQL> create or replace view lguidry_view as
2 select student_id, registration_date
3 from guidry_l;

Create an INSTEAD OF trigger defined on the view created above. This trigger will update the student w/ student_id of 1000 and set the registration date to August 12, 2003.

My textbook doesn't really explain how to create an INSTEAD OF trigger using the UPDATE.Based on my understanding of the below, I sense that an INSTEAD OF trigger isn't appropriate. Are you always overwriting student_id and registration_date with those values? If so, then why not a BEFORE INSERT trigger (if it is Oracle or DB2) on guidry_l table which sets the fields then completes the insert?

If this is off-track, more information will be necessary.

Originally posted by Byrd24
Can someone please help me with the following problem:

SQL> create or replace view lguidry_view as
2 select student_id, registration_date
3 from guidry_l;

Create an INSTEAD OF trigger defined on the view created above. This trigger will update the student w/ student_id of 1000 and set the registration date to August 12, 2003.

My textbook doesn't really explain how to create an INSTEAD OF trigger using the UPDATE.|||Originally posted by dmmac
Based on my understanding of the below, I sense that an INSTEAD OF trigger isn't appropriate. Are you always overwriting student_id and registration_date with those values? If so, then why not a BEFORE INSERT trigger (if it is Oracle or DB2) on guidry_l table which sets the fields then completes the insert?

If this is off-track, more information will be necessary.

I have no idea if I am always overwriting student_id. All I know is that my professor wants us to create an INSTEAD OF trigger using the view that I had created from table guidry_l :

SQL> create table guidry_l (
2 student_id number(8) not null,
3 f_name varchar2(25),
4 l_name varchar2(25),
5 address varchar2(40),
6 zip number(5),
7 phone varchar2(12),
8 registration_date date not null,
9 constraint guidry_l_pk primary key(student_id)
10 );
And update the student w/ id of 1000.|||Re-read your first post and I see what the update is about.
Ok, then here is a template (it is based on DB2, I see an 'OR REPLACE' in your view example, so if you are using Oracle you will need to figure the correct trigger structure syntax -- should be that different):

CREATE TRIGGER x INSTEAD OF UPDATE ON lguidry_view
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
BEGIN
IF (n.student_id = 1000) THEN
UPDATE guidryl SET registration_date = DATE('8/12/2003')
WHERE student_id = N.student_id
END IF;
END

Originally posted by Byrd24
I have no idea if I am always overwriting student_id. All I know is that my professor wants us to create an INSTEAD OF trigger using the view that I had created from table guidry_l :

SQL> create table guidry_l (
2 student_id number(8) not null,
3 f_name varchar2(25),
4 l_name varchar2(25),
5 address varchar2(40),
6 zip number(5),
7 phone varchar2(12),
8 registration_date date not null,
9 constraint guidry_l_pk primary key(student_id)
10 );
And update the student w/ id of 1000.|||Okay I have tried it and I am still having problems. I'm getting the following error:

Warning: Trigger created with compilation errors.

What am I doing wrong?

Create or replace trigger updateguidry
INSTEAD OF UPDATE ON lguidry_view
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
BEGIN
IF (n.student_id = 1000) THEN
UPDATE guidryl SET registration_date = DATE('8/12/2003')
WHERE student_id = N.student_id
END IF;
end updateguidry;
/

Originally posted by dmmac
Re-read your first post and I see what the update is about.
Ok, then here is a template (it is based on DB2, I see an 'OR REPLACE' in your view example, so if you are using Oracle you will need to figure the correct trigger structure syntax -- should be that different):

CREATE TRIGGER x INSTEAD OF UPDATE ON lguidry_view
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
BEGIN
IF (n.student_id = 1000) THEN
UPDATE guidryl SET registration_date = DATE('8/12/2003')
WHERE student_id = N.student_id
END IF;
END|||Try putting a semi-colon at the end of the UPDATE statement.

Originally posted by Byrd24
Okay I have tried it and I am still having problems. I'm getting the following error:

Warning: Trigger created with compilation errors.

What am I doing wrong?

Create or replace trigger updateguidry
INSTEAD OF UPDATE ON lguidry_view
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
BEGIN
IF (n.student_id = 1000) THEN
UPDATE guidryl SET registration_date = DATE('8/12/2003')
WHERE student_id = N.student_id
END IF;
end updateguidry;
/|||Okay I create my trigger a little bit differently:

SQL> CREATE OR REPLACE TRIGGER updateguidry
2 INSTEAD OF UPDATE ON lguidry_view
3 FOR EACH ROW
4 BEGIN
5 IF (:new.student_id = '1000') THEN
6 UPDATE guidry_l
7 SET registration_date = TO_DATE('12-AUG-2003','DD-MON-YYYY')
8 WHERE student_id = :new.student_id;
9 END IF;
10 END updateguidry;
11 /

Trigger created.

I finally created the trigger so now how do I get it to work?? From my understanding, I am suppose to create an update code for it to get it to work. I tried to update the info using the lguidry_view and I got an error message:

ERROR at line 1:
ORA-04098: trigger 'CIS305AD8.LGUIDRY_VIEW' is invalid and failed re-validation

I'm guessing I did the trigger right??|||Change if to IF(:new.student_id = 1000) THEN

Originally posted by Byrd24
Okay I create my trigger a little bit differently:

SQL> CREATE OR REPLACE TRIGGER updateguidry
2 INSTEAD OF UPDATE ON lguidry_view
3 FOR EACH ROW
4 BEGIN
5 IF (:new.student_id = '1000') THEN
6 UPDATE guidry_l
7 SET registration_date = TO_DATE('12-AUG-2003','DD-MON-YYYY')
8 WHERE student_id = :new.student_id;
9 END IF;
10 END updateguidry;
11 /

Trigger created.

I finally created the trigger so now how do I get it to work?? From my understanding, I am suppose to create an update code for it to get it to work. I tried to update the info using the lguidry_view and I got an error message:

ERROR at line 1:
ORA-04098: trigger 'CIS305AD8.LGUIDRY_VIEW' is invalid and failed re-validation

I'm guessing I did the trigger right??

creating trigger to auto set create/modify dates

Hi,

I'm a newbie to sql server and this may be a really dumb question for
some you. I'm trying to find some examples of sql server triggers that
will set columns (e.g. the created and modified date columns) if the row
is 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 before
insert or update trigger and reference old and new instances of the
record. Does sql server have an equivalent? Is there a better way to do
this in sql server?

Thanks
eric

this is what i do in oracle that i'm trying to do in sqlserver...

CREATE OR REPLACE TRIGGER tr_temp_biu
before insert or update
on temp
referencing old as old new as new
for each row
begin
if inserting then
:new.created_date := sysdate;
end if;
:new.modified_date := sysdate;
end tr_temp_biu;On Thu, 07 Oct 2004 13:02:41 -0400, efinney wrote:

>Hi,
>I'm a newbie to sql server and this may be a really dumb question for
>some you. I'm trying to find some examples of sql server triggers that
>will set columns (e.g. the created and modified date columns) if the row
>is 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 before
>insert or update trigger and reference old and new instances of the
>record. Does sql server have an equivalent? Is there a better way to do
>this in sql server?
>Thanks
>eric
>this is what i do in oracle that i'm trying to do in sqlserver...
(snip)

Hi Eric,

Don't try to do a one on one translation from Oracle to SQL Server. The
differences are too big (especially when it comes to triggers and other
non-ANSI-standard syntax).

For the creation date, no trigger is needed. Simply use a default. Use the
same default for the modified date if you want that set at the time a row
is inserted as well. If you rather leave the moodified_date NULL until the
row actually is updated, remove the default and change NOT NULL to NULL.

CREATE TABLE MyTable (KeyCol1 int NOT NULL,
KeyCol2 char(6) NOT NULL,
DataCol1 varchar(130) NULL,
DataCol2 datetime NOT NULL,
Created_Date datetime NOT NULL
DEFAULT (CURRENT_TIMESTAMP),
Modified_Date datetime NOT NULL
DEFAULT (CURRENT_TIMESTAMP),
PRIMARY KEY (KeyCool1, KeyCol2)
)

There are no BEFORE triggers in SQL Server. Only AFTER and INSTEAD OF
triggers are supported. In your case, I'd use an AFTER trigger. A very
fundamental difference that you should really be aware of, is that SQL
Server executes a trigger once per update (insert, delete) statement, with
all affected rows in the inserted and deleted pseudo-tables. All Oracle
triggers I've seen so far are processed for each individual row - a very
big difference that can lead to spectacular errors.

CREATE TRIGGER MyTrigger
ON MyTable
AFTER UPDATE
AS
-- Prevent recursion!
IF NOT UPDATE(Modified_Date)
BEGIN
UPDATE MyTrigger
SET Modified_Date = CURRENT_TIMESTAMP
WHERE EXISTS (SELECT *
FROM inserted AS i
WHERE i.KeyCol1 = MyTable.KeyCol1
AND i.KeyCol2 = MyTable.KeyCol2)
END

(untested)
UPDATE MyTable

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Creating Tables with Triggers

I need an Insert trigger to create a set of tables to my database when i add
new record in the top level master table (horizontal partitioning
denormalization)..
Is this possible using trigger or procedure or even function' and how'
any help appreciated.I'd doing such things by using stored procedure rather within a trigger
Identify that row has beed added and call SP to create a table
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:OI%23fdSb7FHA.1416@.TK2MSFTNGP09.phx.gbl...
>I need an Insert trigger to create a set of tables to my database when i
>add new record in the top level master table (horizontal partitioning
>denormalization)..
> Is this possible using trigger or procedure or even function' and how'
> any help appreciated.
>|||As I said in my other post:
1) Don't multi-post.
2) This is a bad design.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:OI%23fdSb7FHA.1416@.TK2MSFTNGP09.phx.gbl...
>I need an Insert trigger to create a set of tables to my database when i
>add new record in the top level master table (horizontal partitioning
>denormalization)..
> Is this possible using trigger or procedure or even function' and how'
> any help appreciated.
>|||1) I said sorry for posting in 2 groups..
2) there was no reply to my first thread and i'm in need to help
3) Why u think it's a bad design' How gcould i design something better'
hope u could help
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uT635Kc7FHA.1000@.tk2msftngp13.phx.gbl...
> As I said in my other post:
> 1) Don't multi-post.
> 2) This is a bad design.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
> news:OI%23fdSb7FHA.1416@.TK2MSFTNGP09.phx.gbl...
>|||How could i write a procedure to create table "Table_[ID]" Where id is a
paramter..
Also i must use trigger anyway to call this proccedure right'
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uS5H77b7FHA.3044@.TK2MSFTNGP10.phx.gbl...
> I'd doing such things by using stored procedure rather within a trigger
> Identify that row has beed added and call SP to create a table
>
> "Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
> news:OI%23fdSb7FHA.1416@.TK2MSFTNGP09.phx.gbl...
>|||You'll have to be patient. Participation in these groups is voluntary. If
you want immediate help, then open a ticket with PSS.
As for the design, you will have a proliferation of tables, which will be
difficult to control. Why is it that you need all of these tables? Why
can't you have a single table with a column that differentiate? How about
providing a clear business spec - not a program spec?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:eOhRFGd7FHA.956@.TK2MSFTNGP10.phx.gbl...
> 1) I said sorry for posting in 2 groups..
> 2) there was no reply to my first thread and i'm in need to help
> 3) Why u think it's a bad design' How gcould i design something better'
> hope u could help
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uT635Kc7FHA.1000@.tk2msftngp13.phx.gbl...
>|||Thanx for inform me about PSS ticket and sorry for multi-post.
I need to have many tables coz one table will hv more than 6,000,000 record
with 2 text fields one of them is type of "text". which mean searching is so
slow.. so partitioning tables will make each table contain 100,000 to
600,000 record which give reliable search preformance specially with joins
and relations..
The business is database of counties laws and rules, and logically one will
search laws by country, so i partition my tables depend on the the country.
What do u think' do you have better idea'
P.S.. My text is in Arabic so Full Text indexing do not help..
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ejgvjdd7FHA.2716@.TK2MSFTNGP11.phx.gbl...
> You'll have to be patient. Participation in these groups is voluntary.
> If you want immediate help, then open a ticket with PSS.
> As for the design, you will have a proliferation of tables, which will be
> difficult to control. Why is it that you need all of these tables? Why
> can't you have a single table with a column that differentiate? How about
> providing a clear business spec - not a program spec?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
> news:eOhRFGd7FHA.956@.TK2MSFTNGP10.phx.gbl...
>|||SQL Server can handle tables of billions of rows. Typically, you'd place
the text in a separate table, with a FK to the parent. You can place a
Country and County FK column on the parent and index those.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:%23gk6Brd7FHA.2432@.TK2MSFTNGP10.phx.gbl...
> Thanx for inform me about PSS ticket and sorry for multi-post.
> I need to have many tables coz one table will hv more than 6,000,000
> record with 2 text fields one of them is type of "text". which mean
> searching is so slow.. so partitioning tables will make each table contain
> 100,000 to 600,000 record which give reliable search preformance specially
> with joins and relations..
> The business is database of counties laws and rules, and logically one
> will search laws by country, so i partition my tables depend on the the
> country.
> What do u think' do you have better idea'
> P.S.. My text is in Arabic so Full Text indexing do not help..
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ejgvjdd7FHA.2716@.TK2MSFTNGP11.phx.gbl...
>|||this drive to 2 big tables the parent one, and text table one.. this is the
current database structure which need 4 -5 min to search in the text..
this is time isn't acceptable so i will partitioning it to tables which cut
search time to 15 - 30 second (i tested it)
this is big improvment in time.
what do u think'
also could you plz tell me how could i use triggers to create these tables'
thanx
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eiwD66d7FHA.2012@.TK2MSFTNGP14.phx.gbl...
> SQL Server can handle tables of billions of rows. Typically, you'd place
> the text in a separate table, with a FK to the parent. You can place a
> Country and County FK column on the parent and index those.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
> news:%23gk6Brd7FHA.2432@.TK2MSFTNGP10.phx.gbl...
>|||> "Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
> news:%23gWgQfe7FHA.1416@.TK2MSFTNGP09.phx.gbl...

> this drive to 2 big tables the parent one, and text table one.. this is
> the current database structure which need 4 -5 min to search in the text..
> this is time isn't acceptable so i will partitioning it to tables which
> cut search time to 15 - 30 second (i tested it)
Correct indexing will serve you much better than partitioning the table.
Create an index on whatever attribute you would otherwise partitrion the
table by and search on that as well as your other criteria. If "search in
the text" means a free text search then also take a look at the "Full-text
indexing" topics in Books Online. If you look at your earlier thread on this
topic I also made some other suggestions.
David Portas
SQL Server MVP
--

Creating Table Trigger to maintain FuzzyLookup Index

Hi,

I've created initial indexes for my table for the fuzzylookup process. I clicked on "Maintained index" but I don't see any triggers created on the reference table.

Do I create the triggers to maintain indexes myself?

Does anybody know how to create these triggers in terms of schema_name, Data_Modification_Statements etc.?

Would it be "Alter index <index name> REBUILD command?

Appreciate the help.

Gulden

Perhaps you don't have permissions to create the triggers? The "indexes" are index tables, not regular indexes. I'm pretty sure you want it to create the triggers itself. I see there is a sp_FuzzyLookupTableMaintenanceInstall procedure in the master database that you might try. The sp_FuzzyLookupTableMaintenanceUnInstall procedure is documented for removing the triggers, so I'd bet the other one creates them.
|||

Thank you for your reply.

I realized that I was pointing to the development database when I created fuzzy lookup indexes.

I re-created them in the staging database and I can see triggers and index files.

But I cannot run any type of maintenance on them.

I tried running sp_FuzzyLookupTableMaintenanceUnInstall sp_FuzzyLookupTableMaintenanceInvoke

But I get the following error on a recently created index.

A .NET Framework error occurred during execution of user defined routine or aggregate 'sp_FuzzyLookupTableMaintenanceInvoke':

System.Data.SqlClient.SqlException: Could not retrieve metadata from Error Tolerant Index. The index is probably corrupt.

System.Data.SqlClient.SqlException:

Now I am concern that in case of an index corruption in the future I won't be able to uninstall them.

Do you think Just deleting indexes and triggers would work?

Thanks,

|||I don't know what sp_FuzzyLookupTableMaintenanceInvoke does. Do you? Does the UnInstall procedure give that error too? If so, it wouldn't seem that you have any choice but to delete the triggers and index manually. The docs warn that if you delete the table before the triggers, any statements against the reference table will fail until the triggers are removed.
|||

Turned out that I don't have permissions to run those stored procs.

My database admin ran them and got successfully uninstall the triggers.

I had to uninstall them because in the managed code it generated

InsertNonMatchedToClientMap Exception:A .NET Framework error occurred during execution of user defined routine or aggregate 'sp_FuzzyLookupTableMaintenanceInvoke':

System.Data.SqlClient.SqlException: Transaction is not allowed to roll back inside a user defined routine, trigger or aggregate because the transaction is not started in that CLR level. Change application logic to enforce strict transaction nesting.

I think I need to change my database connection strings but we will install in production soon.

So I am back to "Create Index" option on the fuzzy lookup transforms.

Thank you very much for you replies and support....

|||

I receive the following error when selecting the "Maintain Index" option within my SSIS package.

Error: 0xC0202009 at Data Flow Task, Fuzzy Lookup [645]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_FuzzyLookupTableMaintenanceInstall":
System.Data.SqlClient.SqlException: Unable to parse token counts in Error Tolerant Index metadata. The index is probably corrupt.

I am running as "sa" but still don't seem to have permission to execute the SPs. I receive the the following error when executing sp_FuzzyLookupTableMaintenanceInstall from within SQL Management Studio:

Msg 6522, Level 16, State 1, Procedure sp_FuzzyLookupTableMaintenanceInstall, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_FuzzyLookupTableMaintenanceInstall":

System.Data.SqlClient.SqlException: Could not retrieve metadata from Error Tolerant Index. The index is probably corrupt.

System.Data.SqlClient.SqlException:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe)

at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

at Microsoft.SqlServer.Dts.TxBestMatch.TableMaintenance.RaiseErrorId(SqlCommand cmd, FltmErrorMsgId MsgId, FltmErrorState State, SqlServerSeverity Severity)

at Microsoft.SqlServer.Dts.TxBestMatch.TableMaintenance.ReportErrors(SqlCommand cmd, ExceptionType Type, String ErrorMessage, FltmErrorMsgId MsgId, FltmErrorState State, SqlServerSeverity Severity, SqlErrorCollection errors)

at Microsoft.SqlServer.Dts.TxBestMatch.TableMaintenance.TranWrap(DataCleaningOperation c)

.

Any ideas?

Creating Table Trigger to maintain FuzzyLookup Index

Hi,

I've created initial indexes for my table for the fuzzylookup process. I clicked on "Maintained index" but I don't see any triggers created on the reference table.

Do I create the triggers to maintain indexes myself?

Does anybody know how to create these triggers in terms of schema_name, Data_Modification_Statements etc.?

Would it be "Alter index <index name> REBUILD command?

Appreciate the help.

Gulden

Perhaps you don't have permissions to create the triggers? The "indexes" are index tables, not regular indexes. I'm pretty sure you want it to create the triggers itself. I see there is a sp_FuzzyLookupTableMaintenanceInstall procedure in the master database that you might try. The sp_FuzzyLookupTableMaintenanceUnInstall procedure is documented for removing the triggers, so I'd bet the other one creates them.
|||

Thank you for your reply.

I realized that I was pointing to the development database when I created fuzzy lookup indexes.

I re-created them in the staging database and I can see triggers and index files.

But I cannot run any type of maintenance on them.

I tried running sp_FuzzyLookupTableMaintenanceUnInstall sp_FuzzyLookupTableMaintenanceInvoke

But I get the following error on a recently created index.

A .NET Framework error occurred during execution of user defined routine or aggregate 'sp_FuzzyLookupTableMaintenanceInvoke':

System.Data.SqlClient.SqlException: Could not retrieve metadata from Error Tolerant Index. The index is probably corrupt.

System.Data.SqlClient.SqlException:

Now I am concern that in case of an index corruption in the future I won't be able to uninstall them.

Do you think Just deleting indexes and triggers would work?

Thanks,

|||I don't know what sp_FuzzyLookupTableMaintenanceInvoke does. Do you? Does the UnInstall procedure give that error too? If so, it wouldn't seem that you have any choice but to delete the triggers and index manually. The docs warn that if you delete the table before the triggers, any statements against the reference table will fail until the triggers are removed.
|||

Turned out that I don't have permissions to run those stored procs.

My database admin ran them and got successfully uninstall the triggers.

I had to uninstall them because in the managed code it generated

InsertNonMatchedToClientMap Exception:A .NET Framework error occurred during execution of user defined routine or aggregate 'sp_FuzzyLookupTableMaintenanceInvoke':

System.Data.SqlClient.SqlException: Transaction is not allowed to roll back inside a user defined routine, trigger or aggregate because the transaction is not started in that CLR level. Change application logic to enforce strict transaction nesting.

I think I need to change my database connection strings but we will install in production soon.

So I am back to "Create Index" option on the fuzzy lookup transforms.

Thank you very much for you replies and support....

|||

I receive the following error when selecting the "Maintain Index" option within my SSIS package.

Error: 0xC0202009 at Data Flow Task, Fuzzy Lookup [645]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_FuzzyLookupTableMaintenanceInstall":
System.Data.SqlClient.SqlException: Unable to parse token counts in Error Tolerant Index metadata. The index is probably corrupt.

I am running as "sa" but still don't seem to have permission to execute the SPs. I receive the the following error when executing sp_FuzzyLookupTableMaintenanceInstall from within SQL Management Studio:

Msg 6522, Level 16, State 1, Procedure sp_FuzzyLookupTableMaintenanceInstall, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_FuzzyLookupTableMaintenanceInstall":

System.Data.SqlClient.SqlException: Could not retrieve metadata from Error Tolerant Index. The index is probably corrupt.

System.Data.SqlClient.SqlException:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe)

at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

at Microsoft.SqlServer.Dts.TxBestMatch.TableMaintenance.RaiseErrorId(SqlCommand cmd, FltmErrorMsgId MsgId, FltmErrorState State, SqlServerSeverity Severity)

at Microsoft.SqlServer.Dts.TxBestMatch.TableMaintenance.ReportErrors(SqlCommand cmd, ExceptionType Type, String ErrorMessage, FltmErrorMsgId MsgId, FltmErrorState State, SqlServerSeverity Severity, SqlErrorCollection errors)

at Microsoft.SqlServer.Dts.TxBestMatch.TableMaintenance.TranWrap(DataCleaningOperation c)

.

Any ideas?

Creating SQL triggers thru managed code

Hi,
I am using SQL Server 2005 and .NET Framework 2.0.
I need to create a trigger using managed code assembly. I did following
steps:
1. Wrote a .NET class that implements the functionality of the trigger.
2. Compiled the class to produce .NET assembly
3. Registered that assembly using CREATE ASSEMBLY statement
4. Created trigger definition using CREATE TRIGGER statement as
following
<snip>
CREATE TRIGGER Email
ON dbo.Users
FOR INSERT
AS EXTERNAL NAMES UsersTrigger.CLRTriggers.EmailAudit
</snip>
where UsersTrigger, CLRTriggers and EmailAudit are the names of
assembly,
class and method respectively.
But when I execute this statement, I receive error as:- "Could not find
Type
'CLRTriggers' in assembly 'UsersTrigger'".
Anybody knows where I might be going wrong?
Thanks,
ManaGot it. Write Create Trigger statement as follows:
CREATE TRIGGER Email
ON dbo.Users
FOR INSERT
AS EXTERNAL NAMES UsersTrigger.[UsersTrigger.CLRTriggers].EmailAudit
Dont know why u need to mention assembly name again with the class name
!
Mana :)

Creating SQL Triggers

Helllo,
I've done these in the past but it's been a while.
I'd like to create a trigger on a SQL table to track any time a record
in this tables gets updated with a zero value in one of the table
columns.
Client is having an issue where an estimated cost field is getting set
to zero by a certain process and I need a tool to track down the
culprit.
I'd like to trap for this column being set to zero on any update or
insert into this table and then dump this audit trail (user ID, spid,
time, date, etc..) out to a manually created sql table.
Any advice or articles that could help would be much appreciated.
Thanks,
JonCREATE TRIGGER NoZerosPlease ON [YourTable]
FOR UPDATE
AS
BEGIN
INSERT INTO [AuditTable]
SELECT SYSTEM_USER, @.@.SPID, APP_NAME(),
ISNULL(OBJECT_NAME(@.@.PROCID),'Not an sp'),
GETDATE(), d.[YourColumn] AS "PreviousValue"
FROM inserted i INNER JOIN deleted d on i.[PKColumn] = d.[PKColumn]
--more on conditions if you have a composite primary key
WHERE i.[YourColumn] = 0
END
You'll need to create the [AuditTable] table.
--
"JMo" wrote:

> Helllo,
> I've done these in the past but it's been a while.
> I'd like to create a trigger on a SQL table to track any time a record
> in this tables gets updated with a zero value in one of the table
> columns.
> Client is having an issue where an estimated cost field is getting set
> to zero by a certain process and I need a tool to track down the
> culprit.
> I'd like to trap for this column being set to zero on any update or
> insert into this table and then dump this audit trail (user ID, spid,
> time, date, etc..) out to a manually created sql table.
> Any advice or articles that could help would be much appreciated.
> Thanks,
> Jon
>|||Thank You Mark!!
Okay, I plan to create a table with a counter as a primary key and then
create a table with string fields to catch user, spid, and app name and
a date field the getdate function.
Am I on the right track?
Thanks again,
Jon
Mark Williams wrote:
> CREATE TRIGGER NoZerosPlease ON [YourTable]
> FOR UPDATE
> AS
> BEGIN
> INSERT INTO [AuditTable]
> SELECT SYSTEM_USER, @.@.SPID, APP_NAME(),
> ISNULL(OBJECT_NAME(@.@.PROCID),'Not an sp'),
> GETDATE(), d.[YourColumn] AS "PreviousValue"
> FROM inserted i INNER JOIN deleted d on i.[PKColumn] = d.[PKColumn]
> --more on conditions if you have a composite primary key
> WHERE i.[YourColumn] = 0
> END
> You'll need to create the [AuditTable] table.
> --
> "JMo" wrote:
>

Creating Multiple Triggers is same sql script

I'm trying to use Query Analyzer to create several triggers on different files in the same sql script file. It appears to only allow me to create one trigger at a time in Query Analyzer. How do you separate multiple create trigger statements? Here what I'm trying to do:

CREATE TRIGGER PO_BOL_DELETE ON dbo.PO_BOL
FOR DELETE
AS

INSERT into PO_Back
SELECT *, host_name(), suser_name(), getdate()
FROM deleted
GO

CREATE TRIGGER RECEIPT_DELETE ON dbo.receipt
FOR DELETE
AS

INSERT into receipt_Back
SELECT *, host_name(), suser_name(), getdate()
FROM deleted
GOWell that's the way to do it...(except for the SELECT * bit)

http://weblogs.sqlteam.com/brettk/archive/2004/04/22/1272.aspx

Are you getting an error?

select * is dangerous btw.|||It stops at the second create trigger and indicates create trigger not valid. Should the syntax I have work?|||works for me...

USE Northwind
GO

CREATE TABLE PO_BOL(Col1 int)
CREATE TABLE receipt(Col1 int)
GO

CREATE TRIGGER PO_BOL_DELETE ON dbo.PO_BOL
FOR DELETE
AS

INSERT into PO_Back
SELECT *, host_name(), suser_name(), getdate()
FROM deleted
GO

CREATE TRIGGER RECEIPT_DELETE ON dbo.receipt
FOR DELETE
AS

INSERT into receipt_Back
SELECT *, host_name(), suser_name(), getdate()
FROM deleted
GO

DROP TABLE PO_BOL
DROP TABLE receipt
GO

Couple of things...lose SELECT *, Make sure you supply the column list for the insert...other than that it all looks good|||Thanks for the help! I got it to work..... Thanks again.

Another question:

I'm using VB to open a direct connection to SQL SERVER 2000. The AnsiNPW=off in the connection doesn't work. Any ideas? I had to create my tables in SQL server with SET ANSI_PADDING OFF to get the spaces trimmed.

Any thoughts?

JGS|||I'll tell anone who'll listen that I've forgot all my VB...otherwise they might make me build interfaces...

Just talking about it and I feel all dirty...

How is the table defined?

char or varchar??|||Your on to it! The fields are Varchar. I tried changing the fields to Char but AnsiNPW still doesn't seem to do anything in the connection string. Have you heard of any bug that AnsiNPW doesn't work?