I have 2 tables (table1 and table2) - these are the same tables (table 2 is a backup of table 1)
I want that every time a row gets updated or a new row entered to trigger an event that will update table 2
As far5 as the table, any column(s) can be update and there are 50+ columns
The problem i am having when doing the below trigger is that when an update or insert is done on table1, table1 gets appended to table2 which then has duplicates.
I thought this was right - (substring(columns_updated(),1,1)) > 0 - to check if a column has been changed within a row
Can you please modify the trigger or If there is any other way??
CREATE TRIGGER trig_test
INSERT INTO table2
SELECT * FROM table1 where (substring(columns_updated(),1,1)) > 0
all the tables used are import form a dbf IV file
when it imports, it sets all data types to varchar and it does not set any primary keys (the only thing it keeps is the column names and size from the dbf file)
but there is a column that is unique by account numbers ( but not set to unique) (and would be to difficult to set it to unique due to an out sourced programmer creates the account numbers which means i can't modify the code nor tables (i know bad idea)
but anyways instead of primarykey i can use accountno ?
Delete from table2 inner join inserted on table2.Accountno = inserted.Accountno
PS using your way i get error "Incorrect syntax near the keyword 'inner'.
" Playing around with the code now.